Building an SQL query for GROUP_CONCAT-ing multiple one/many-to-many tables

Question

I have four tables, person, email, organ and person_organ and:

• person and email has one-to-many relation;
• person and organ has many-to-many relation through person_organ.

Question, how to I write a query so I have a set with columns full name, all email and all organ for persons in a particular organ?

This is as far as I get, using GROUP_CONCAT to combine emails grouped by person:

select person.id as 'ID', 
       organ.short_name as 'Organ', 
       CONCAT ( person.first_name, ' ', person.last_name) as 'Full Name', 
       GROUP_CONCAT(email.email SEPARATOR ', ') as 'Emails'
    from person 
  left join person_organ on person.id = person_organ.person_id 
  join organ on organ.id = person_organ.organ_id
  join email on email.person_id = person.id
  where person_organ.organ_id = 1
  group by person.id;

What can I add to the above query so that another column is added that shows all the groups that this person belongs to?

What I have so far:

+----+-------+--------------+----------------------------+
| ID | Organ | Full Name    | Emails                     |
+----+-------+--------------+----------------------------+
|  1 | SC    | John Doe     | [email protected], [email protected] |
|  2 | SC    | Richard Rowe | [email protected]               |
|  3 | SC    | San Zhang    | [email protected]           |
|  4 | SC    | Taro Yamada  | [email protected]             |
+----+-------+--------------+----------------------------+

What I want, is to have the Organs as an additional column to the above result set, in addition to the singular Organ field:

+----+-------+--------------+----------------------------+------------+
| ID | Organ | Full Name    | Emails                     | Organs     |
+----+-------+--------------+----------------------------+------------+
|  1 | SC    | John Doe     | [email protected], [email protected] | SC, EC     |
|  2 | SC    | Richard Rowe | [email protected]               | SC, EC, HR |
|  3 | SC    | San Zhang    | [email protected]           | SC, HR     |
|  4 | SC    | Taro Yamada  | [email protected]             | SC, EC, HR |
+----+-------+--------------+----------------------------+------------+

Thank you very much for your answer! I am quite new to making real applications and I feel that this should be quite a common task. Any help on pointing to the right resources is much welcome!

-- update: SQLfiddle link: http://www.sqlfiddle.com/#!9/bc08de/40

Answer 1

http://www.sqlfiddle.com/#!9/bc08de/14/0 I am not much aware of the syntax in MySQL but I have tried this and got to this result. The id that is 1 has duplicates. Maybe because of some syntax. just have a look at it Have got the desired result.