MySQL – How to Join Data Using GROUP_CONCAT

MySQL

I can't get the right output. I have two tables department and worker:

    department
    +----+----------------------------------------+
    | id | NAME                                   |
    +----+----------------------------------------+
    |  1 | Academic Affairs Information Technology |
    |  2 | Business and Financial Services        |
    |  3 | Arts & Lectures                        |
    |  4 | Anthropology                           |
    |  5 | Academic Personnel                     |
    |  6 | Computer Science                       |
    |  7 | Department of Theatre and Dance        |
    |  8 | Department of Communication            |
    +----+----------------------------------------+

    worker
    +---------+------------+-------------------+------------------+---------------+
    | work_id | firstname  | lastname          | middlename       | department_id |
    +---------+------------+-------------------+------------------+---------------+
    |       1 | John       | Doe               | Jdoe             |             2 |
    |       2 | Carlos     | Pereira Fernandes | Caito            |             3 |
    |       3 | Sergey     | Lavrov            | tough_politician |             4 |
    |       4 | Alexander  | Povetkin          | Sasha            |             5 |
    |       5 | John       | Smith             | js               |             3 |
    |       6 | Michael    | Jordon            | Mj               |             5 |
    |       7 | Sue        | Smith             | Ss               |             3 |
    |       8 | Sophy      | Doe               | Sdoe             |             6 |
    +---------+------------+-------------------+------------------+---------------+

This is the result I want to achieve:

    | depart_name                     | worker_IDs |
    +----------+---------------------------------+-
    | Business and Financial Services | 1       |
    | Arts & Lectures                 | 2, 5, 7 |
    | Anthropology                    | 3       |
    | Academic Personnel              | 4,6     |
    | Computer Science                | 8       |
    +----------+--------------------------------- 

But my SQL query gives me this:

  SELECT w.work_id, 
         GROUP_CONCAT(department_id) worker_ids, 
         GROUP_CONCAT(name) department_names 
  FROM worker w 
  JOIN DEPARTMENT d 
  ON  (w.department_id = d.id)
  GROUP BY work_id;

    +---------+------------+---------------------------------+
    | work_id | worker_ids | department_names                |
    +---------+------------+---------------------------------+
    |       1 | 2          | Business and Financial Services |
    |       2 | 3          | Arts & Lectures                 |
    |       3 | 4          | Anthropology                    |
    |       4 | 5          | Academic Personnel              |
    |       5 | 3          | Arts & Lectures                 |
    |       6 | 5          | Academic Personnel              |
    |       7 | 3          | Arts & Lectures                 |
    |       8 | 6          | Computer Science                |
    +---------+------------+---------------------------------+

Best Answer

According to your desired result, you should group_concat workers group by department's name.

select d.name, group_concat(w.work_id) workers_id
from   department d
join   worker w
on     w.department_id = d.id
group by d.name;
name                            | workers_id
:------------------------------ | :---------
Academic Personnel              | 6,4       
Anthropology                    | 3         
Arts & Lectures                 | 5,2,7     
Business and Financial Services | 1         
Computer Science                | 8         

dbfiddle here