I have 3 tables.
1. user
2. user_role
3. user_role_mapping
Sample record,
user
+--------+----------+---------------+
| userid | username | email |
+--------+----------+---------------+
| 1 | user1 |user1@test.com |
| 2 | user2 |user2@test.com |
+--------+----------+---------------+
user_role
+--------+----------+
| roleid | rolename |
+--------+----------+
| 1 | user |
| 2 | manager |
| 3 | director |
| 4 | admin |
+--------+----------+
User_role_mapping
+--------+------+
| roleid |userid|
+--------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+--------+------+
Query
select u.userid, u.username, u.email,
count(case when ur.rolename = 'user' THEN 1 END) user,
count(case when ur.rolename = 'manager' THEN 1 END) manager,
count(case when ur.rolename = 'director' THEN 1 END) director,
count(case when ur.rolename = 'admin' THEN 1 END) admin
from user_role ur
left join userrole_mapping urm
on ur.roleid = urm.roleid
left join user u
on urm.userid = u.userid
group by u.userid, u.username, u.email
Result:-
+--------+----------+---------------+------|---------|----------|-------|
| userid | username | email | user | manager | director | admin |
+--------+----------+---------------+------------------------------------
| 1 | user1 |user1@test.com | 1 | 1 | 1 | 1 |
+--------+----------+---------------+------------------------------------
Here, I am hardcoding the role in the query and the new role can get added in the future and I do not want to do the code change. Is there any better approach? I am fine with comma separated result for role in the result.
For comma separation you can use group_concat:
select u.user_id, u.user_name, u.email,
Group_concat(ur.role_name) roles
from user u
left join user_role_mapping urm
on urm.user_id = u.user_id
left join user_role ur
on ur.role_id = urm.role_id
where u.user_id = ?
group by u.user_id;
Another, maybe better approach w/o hardcoding would be to query the roles separately:
select u.user_id, u.user_name, u.email, ur.role_name
from user u
left join user_role_mapping urm
on urm.user_id = u.user_id
left join user_role ur
on ur.role_id = urm.role_id
where u.user_id = ?;