MySQL Left Join Many to One Row

Question

To simplify my problem: Let's say I have 3 tables.

Rooms              People                    Things
--------           --------                  --------
id| name           id | name | fk_rooms      id | name | fk_rooms
-----------        ---------------------     ---------------------
1 | kitchen        1  | John | 1              1 | TV   | 2
2 | bedroom        2  | Mary | 2              2 | bed  | 2
                   3  | Andy | 1              3 | sink | 1
                   4  | Laura| 1

Now I'm doing something like:

SELECT r.name AS room_name, p.name AS name, t.name AS thing FROM Rooms r 
LEFT JOIN People p ON p.fk_rooms = r.id
LEFT JOIN Things t ON t.fk_rooms = r.id

which in my case works perfectly except for a few that have many to one relationship with the "Rooms" table. So instead of new rows in the result set holding the different names for "People" and "Things" in relation to the "Rooms" table, I would like to receive only two rows:

1. kitchen, John, Andy, Laura, sink
2. bedroom, Mary, TV, bed

A GROUP BY on r.id will only select one row from each table. Any help is highly appreciated!

Answer 1

Here it is what you're looking for:

SELECT r.name AS room_name, 
   GROUP_CONCAT(p.name separator ',') AS people_name, 
   GROUP_CONCAT(t.name separator ',') AS things
FROM Rooms r 
LEFT JOIN People p ON p.fk_rooms = r.id
LEFT JOIN Things t ON t.fk_rooms = r.id
GROUP BY r.id