Search code examples
mysqlsqlselectgroup-concat

MySQL Contact multiple right joined rows into one column

If I have this

users

id user

1 john   
2 tom

and

states

user_id state

1        MA
1        CA
2        VA

How do I get a result set that is:

id user states


1 john  MA,CA
2 tom   VA

I think it has something to do with group contact, but I can't get it to work.

Solution

  • As you suggested, group_concat is the way to go - you just need to group by the users' columns:

    SELECT   u.id, u.user, GROUP_CONCAT(s.state)
FROM     users u
JOIN     states s ON u.id = s.user_id
GROUP BY u.id, u.user