How to join same department with multiple staff? 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 ' &lt;,&gt; ') 'fullName' ,
GROUP_CONCAT(s.email SEPARATOR ' &lt;,&gt; ') '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 &lt;,&gt; Tom    | richard@ &lt;,&gt; tom@  | 
| HR         |  Andy               | andy@              | 
| IT         |  Elsa &lt;,&gt; Leo       | elsa@ &lt;,&gt; 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?
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 ' &lt;,&gt; '), GROUP_CONCAT(s.email SEPARATOR ' &lt;,&gt; ')
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