0

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                |
    +---------+------------+---------------------------------+
  • I can't figure out what is wrong? Thanks in advance – Carlos Mar 5 '18 at 10:46
  • Look at what you want to achieve, it is pretty obvious what you should select and group by to accomplish that. For example, why do you think it is a good idea to select and group by w.work_id? – Lennart Mar 5 '18 at 11:01
  • You can edit your question by clicking edit button at bottom. – McNets Mar 5 '18 at 11:03
  • According to your desired result, you should group by department_id – McNets Mar 5 '18 at 11:05
  • McNets , thanks for the answer! I have just changed the code a bit and got the right results – Carlos Mar 5 '18 at 11:06
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.