CODEWITHSUNDEEP
mysqlselectgroup-concat
Kotanet
Kotanet

Reputation: 573

GROUP_CONCAT on two different tables

I've got three tables:

staff

id  name
1   Alex
2   John
3   Peter

staff_roles

id  role_name
1   Barber
2   Driver

assigned_roles

id  staff_id  role_id
1   1         1
2   1         2
3   2         1
4   3         2

I want to get output smth. like:

name   roles
Alex   [{'id':'1','role_name':'Barber'},{'id':'2','role_name':'Driver'}]
John   [{'id':'1','role_name':'Barber'}]
Peter  [{'id':'2','role_name':'Driver'}]

I'm doing:

SELECT
   staff.name,
   GROUP_CONCAT(DISTINCT assigned_roles.role_id) as roles
FROM staff
   LEFT JOIN assigned_roles
      ON assigned_roles.staff_id = staff.id
   LEFT JOIN roles
      ON roles.id = assigned_roles.role_id
GROUP BY
   staff.id

But how can I add role name to GROUP_CONCAT to have the output I want?

Upvotes: 0

Views: 606

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can construct the final result like this:

SELECT s.name,
       CONCAT('[',
              GROUP_CONCAT(DISTINCT '{''id'':''', ar.role_id, ''',''role_name'':''', r.role_name, '''}'),
              ']')
FROM staff s LEFT JOIN
     assigned_roles ar
     ON ar.staff_id = s.id LEFT JOIN
     roles r
     ON r.id = ar.role_id
GROUP BY s.id;

There might be a typo with all those single quotes and unusual characters.

Upvotes: 1

Related Questions