MySQL list users and their groups

Question

In MySql database I have 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... Member of the Red group is Anna, member of the Green gorup is Joe and members of the Blue group are Joe and max.

How can I get the list of users and their groups

User  | Group
------+------------
Joe   | Green, Blue
Anna  | Red
Max   | Blue

group is a keyword please alter the table name group to some else! — Suleman Ahmad, Apr 23 '12 at 14:20

score 7 · Answer 1 · edited Apr 23 '12 at 14:16

7

You can get it done using the GROUP_CONCAT function of MySQL. Note that this makes your query non-portable (since 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've assumed you have reasonable primary/unique keys on the tables (e.g. a given user name cannot be listed twice in the same group).

edited Apr 23 '12 at 14:16

Quassnoi

413,100
91
616
614

answered Apr 23 '12 at 14:14

Romain

12,679
3
41
54

1

+1 Note `GROUP_CONCAT` has a max length defined by [group_concat_max_len](http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_group_concat_max_len). It defaults to 1024 but can be changed. So if your data looks like it's being truncated, that's why. – Mike B Apr 23 '12 at 14:20
@MikeB: Good point. Though if that gets to be a problem, then the listing is better generated in application code rather than DB IMO. – Romain Apr 23 '12 at 14:21

score 0 · Accepted Answer · answered Apr 23 '12 at 14:22

0

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

answered Apr 23 '12 at 14:22

Nesim Razon

9,684
3
36
48

score 0 · Answer 3 · edited Jul 23 '12 at 06:42

0

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;

edited Jul 23 '12 at 06:42

Taz

3,718
2
37
59

answered Apr 26 '12 at 05:39

apc

1

MySQL list users and their groups

3 Answers3