Mysql – How to join same department with multiple staff

MySQL

I have 2 tables.

staff

+----+- ----------+-----------+-------------+------------+
| ID | fullname   | email     | countryCode | departCode | 
+----+------------+-----------+-------------+------------+
| 1  |  Richard   | richard@  | 1           | 1          | 
| 2  |  Tom       | tom@      | 1           | 1          |
| 3  |  Andy      | andy@     | 2           | 2          |
| 4  |  Elsa      | elsa@     | 2           | 3          |
| 5  |  Leo       | leo@      | 1           | 3          |

department

+------------+- ------  ----+
| departCode | departName   |
+------------+--------------+
| 1          |  Management  |
| 2          |  HR          | 
| 3          |  IT          |

The 1st method group_cat for my query the output:

select d.departName , 
GROUP_CONCAT(s.fullName SEPARATOR ' <,> ') 'fullName' ,
GROUP_CONCAT(s.email SEPARATOR ' <,> ') 'email' from staff s inner join department d on s.departCode = d.departCode 
where s.departCode and s.countryCode=2 
group by d.departCode

Gives the output:

+------------+---------------------+--------------------+
| departName | fullname            | email              |
+------------+---------------------+--------------------+
| Management |  Richard <,> Tom    | richard@ <,> tom@  | 
| HR         |  Andy               | andy@              | 
| IT         |  Elsa <,> Leo       | elsa@ <,> leo@     |

The 2nd Method I use:

SELECT d.departName, s.fullName, s.position, s.site, s.ext, s.did, s.mobile, s.email from staff s 
left join department d on s.departCode = d.departCode 
left join country c on s.countryCode = c.countryCode 
where c.countryCode = 2

Gives the output:

+------------+---------------------+--------------------+
| departName | fullname            | email              |
+------------+---------------------+--------------------+
| Management |  Richard            | richard@           |
| Management |  Tom                | tom@               | 
| HR         |  Andy               | andy@              | 
| IT         |  Elsa               | elsa@              |
| IT         |  Leo                | leo@               |

How can I use 2nd method to combine the department with multiple staff and result show multiple staff in one department?

Best Answer

Do you mean you want to left join but then show single departments with the staff concatenated?

SELECT d.departName, GROUP_CONCAT(s.fullName SEPARATOR ' <,> '), GROUP_CONCAT(s.email SEPARATOR ' <,> ')
from staff s 
left join department d on s.departCode = d.departCode 
left join country c on s.countryCode = c.countryCode 
where c.countryCode = 2
group by d.departName

Related Solutions

Mysql – execute a prepared statement inside a MySQL SELECT

From the question, it looks like what you have written quasi-resembles what Oracle calls an anonymous code block. MySQL does not facilitate such a mechanism.

With regard to what you want to accomplish, you do not need a stored procedure.

Try assembling the SQL statement like this:

@stmt = 'SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs FROM buyerList AS b ';
@stmt = CONCAT(@stmt,'LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass ');
@stmt = CONCAT(@stmt,'LEFT JOIN pricelists p ON ');
SELECT GROUP_CONCAT(CONCAT('(p.sid = a.sid AND p.preisliste = ',sid,' AND p.ean = a.ean AND p.iln = ',pricelist',)') SEPARATOR ' OR ')
INTO @LeftJoinClause FROM preislisten;
@stmt = CONCAT(@stmt,@LeftJoinClause,' WHERE b.bid = ?');
SELECT @stmt\G

This will print out the desired query

If it is the desired query, execute it

PREPARE sql FROM @stmt;
EXECUTE sql using @param_iln;
DEALLOCATE PREPARE sql;

Give it a Try !!!

If you actually want the query to be small without hardcoding every value just write the code with a more straightforward LEFT JOIN setup

SELECT p.sid, ifnull(p.pricelist,"BASE"), count(*) AS recs FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
LEFT JOIN pricelists p ON
    (p.sid = a.sid AND p.preisliste = a.sid AND p.ean = a.ean)
WHERE b.bid = ?
GROUP BY p.sid,p.pricelist;

I also just noticed you have a.sid and a.ean, what table has an alias of a ?

Mysql – Slow performance of MySQL Join Query

Please provide SHOW CREATE TABLE; the explain is useless without it.

OR is a performance killer in many contexts.

( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR ( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ...

Turn that into

JOIN ( SELECT id FROM p WHERE 
( p.pricelist = "name_abc" AND p.iln = "sellerID_123" ) OR 
( p.pricelist = "name_def" AND p.iln = "sellerID_456" ) OR ... ) x ON x.id = foo.id

Also needed (on p):

INDEX(pricelist, iln, id)

(With the CREATEs, I could be more specific.)

The idea behind this "trick" is to move the costly work of the OR into a subquery that returns the necessary ids. Plus the INDEX makes it so that it can do all that work in the INDEX.

Best Answer

Related Solutions

Mysql – execute a prepared statement inside a MySQL SELECT

Mysql – Slow performance of MySQL Join Query

Related Question