Как объединить данные из одного поля в списке с разделителями-запятыми в отношениях "многие-ко-многим" в MySQL?

У меня есть отношение "многие ко многим" между людьми и департаментами, поскольку один человек может быть во многих отделах.

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

Я хочу это:

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

Если я делаю простые LEFT JOINs в таблицах с использованием SQL ниже, я получу несколько строк, относящихся к одному человеку:

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

Я пробовал различные комбинации GROUP_CONCAT, но не повезло.

Любые идеи поделиться?

Ответы

Ответ 1

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

Вывод:

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

Ответ 2

Мое решение:

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

результат:

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