How to concatenate data from one field, in a comma-delimited list, in a many-to-many relationship in MySQL?

Question

I have a many-to-many relationship between People and Departments since one person can be in many departments.

People          Departments
------          -----------
pID  pName      deptID   deptName
1    James      1        Engineering
2    Mary       2        Research
3    Paul       3        Marketing
                4        Communications

People_Departments
------------------
pID   deptID
1     1
1     2
2     2
2     4
3     1
3     2
3     3

What I want is this:

pName  deptName
James  Engineering, Research
Mary   Research, Communication
Paul   Engineering, Research, Marketing

If I do plain LEFT JOINs on the tables using the SQL below, I will get several rows related to one person:

SELECT people.pName,
       departments.deptName
FROM people
LEFT JOIN people_departments ON people.pID=people_departments.pID
LEFT JOIN departments ON people_departments.deptID=departments.deptID

I have tried various combinations of GROUP_CONCAT but without luck.

Any ideas to share?

Shef · Accepted Answer · 2020-02-11T21:02:28.060

21

    SELECT people.pName,
           GROUP_CONCAT(departments.deptName SEPARATOR ', ') deptName
      FROM people
 LEFT JOIN people_departments 
        ON people.pID = people_departments.pID
 LEFT JOIN departments 
        ON people_departments.deptID = departments.deptID
  GROUP BY people.pID

Output:

+-------+----------------------------------+
| pName | deptName                         |
+-------+----------------------------------+
| James | Engineering, Research            |
| Mary  | Research, Communications         |
| Paul  | Engineering, Research, Marketing |
+-------+----------------------------------+
3 rows in set (0.00 sec)

edited Feb 11 '20 at 21:02

answered Jul 24 '11 at 21:40

Shef

44,808
15
79
90

1

Actually, none of my combinations worked because I omitted the GROUP BY, which is very important. I fetch data from several many-to-many-related tables in my actual project, and I managed to get the data I want with your help. Thanks again! – Shailen Jul 24 '11 at 22:27
Note that in this query the LEFT JOIN with `people_departments` will be converted to INNER JOIN, because `people_departments.deptID` is used in the ON clause of an INNER JOIN. If you want to list people without departments, both JOINs should be LEFT JOINs. – Paul Spiegel Feb 10 '20 at 17:10
@PaulSpiegel Good catch. Updated the query. – Shef Feb 11 '20 at 21:02

score 0 · Answer 2 · answered Jun 01 '17 at 10:42

My solution is:

SELECT people.pName,
GROUP_CONCAT(tmp.deptName SEPARATOR ', ') deptName
FROM people 
LEFT JOIN (SELECT people_departments.pID, departments.deptName FROM people_departments LEFT JOIN departments  ON people_departments.deptID = departments.deptID) as tmp
ON tmp.pID = people.pID
GROUP BY people.pID

result:

+-------+----------------------------------+
| pName | deptName                         |
+-------+----------------------------------+
| James | Engineering, Research            |
| Mary  | Research, Communications         |
| Paul  | Engineering, Research, Marketing |
+-------+----------------------------------+

How to concatenate data from one field, in a comma-delimited list, in a many-to-many relationship in MySQL?

2 Answers2

Linked

Related