Many to many query MySQL

Question

I have two tables: users [id, email] and roles [id, name] with many-to-many connection table user_has_roles [user_id, role_id]. I'm trying to get table which showing which user has particular role and if not then null (or 0) similar to following:

            | role 1 | role 2 | role 3
---------------------------------------
user1@email | 1      | null   | 1
user2@email | null   | null   | 1
---------------------------------------

Much appreciate any help on this.

juergen d juergen d · Accepted Answer · 2017-06-06T11:27:16

select u.email, 
       case when sum(r.name = 'role 1') > 0 then 1 else null end as `role 1`,
       case when sum(r.name = 'role 2') > 0 then 1 else null end as `role 2`,
       case when sum(r.name = 'role 3') > 0 then 1 else null end as `role 3`
from users u
left join user_has_roles ur on ur.user_id = u.id
left join roles r on ur.role_id = r.id
group by u.email

Many to many query MySQL

1 Answers