SQL Using group_concat

Question

I have 3 tables :

Role :

id	name	user_id
1	president	1
2	chief	1
3	caporal	1
4	president	2
5	caporal	3

Group :

id	name	user_id
1	test1	1
2	test2	3
3	test3	1
4	test1	2
5	test3	2

User

id	username
1	user1
2	user2
3	user3

I would like to select all group with a field condition : if userX have more than 0 role and if in these roles it has 'president' then return 1 otherwise 0.

So having data like this :

user_id	username	role_condition	group_name
1	user1	1	test1
1	user1	1	test3
2	user2	1	test1
2	user2	1	test3
3	user3	0	test2

I tried this :

SELECT u.id,
       u.username,
       if(LOCATE('president', group_concat(u.role)) > 0,1,0),
       g.name
FROM Group g
LEFT JOIN User u ON u.id = g.user_id
LEFT JOIN Role r ON r.user_id = u.id

Without a group by it gives me only 1 row because of the group_concat, and I can't use the group by because if I do that on user_id or username it will not give me all the rows I want.

I don't know how to do that and having the result I want.

Thank for your help :)

Answer 1

You can do it without aggregation, with EXISTS:

SELECT u.id, u.username,
       EXISTS (
         SELECT 1 FROM Role r
         WHERE r.user_id = u.id AND r.name = 'president'
       ) role_condition, 
       g.name
FROM `User` u INNER JOIN `Group` g
ON g.user_id = u.id
ORDER BY u.id

If you want all the users, even those without any group, change to a LEFT join.

See the demo.
Results:

id	username	role_condition	name
1	user1	1	test1
1	user1	1	test3
2	user2	1	test1
2	user2	1	test3
3	user3	0	test2