Geek asks and answers

MySQL lists users and their groups

In MySql database, I have a table user

user_id | user_name
--------+----------
1       | Joe
2       | Anna
3       | Max

Table group

group_id | group_name
---------+----------
1        | Red
2        | Blue
3        | Green

Table group_member

group_member_id | user_id | group_id
----------------+---------+---------
1               | 1       | 2
2               | 3       | 2
3               | 1       | 3 
3               | 2       | 1

So ... A member of the red group is Anna, a member of the Green gorup is Joe, and members of the Blue group are Joe and max.

How to get a list of users and their groups

User  | Group
------+------------
Joe   | Green, Blue
Anna  | Red
Max   | Blue
+3
source share
3 answers
SELECT u.user_name, GROUP_CONCAT(g.group_name)
FROM `user` u
inner JOIN group_member gm on gm.user_id = u.user_id
inner JOIN `group` g on g.group_id = gm.group_id
group by u.user_id
0
source

You can do this using the GROUP_CONCATMySQL function . Please note that this makes your query not portable (using MySQL extensions):

SELECT user_name, GROUP_CONCAT(group_name)
FROM user
JOIN group_member USING (user_id)
JOIN group USING (group_id)
GROUP BY
       user_id

I assumed that you have reasonable primary / unique keys in the tables (for example, this username cannot be specified twice in the same group).

+5
source 
select u.user_name as users, group_concat(g.group_name) as groups
from `user` u 
join group_member gm on gm.user_id = u.user_id
join `group` g on g.group_id = gm.group_id
group by u.user_id;
0
source

More articles:


All Articles