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 am 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 works fine in my case, except for a few that have a lot to do with the Numbers table. Therefore, instead of new lines in the result set containing different names for "People" and "Things" in relation to the "Rooms" table, I would like to get only two lines:

 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 much appreciated!

Answer 1

Here is what you are 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 

Answer 2

Yes, you need a group by clause, and you need to use the GROUP_CONCAT function. You must group your results using People.fk_rooms and Thing.fk_rooms.

Perhaps you could use two different queries: the first will bring together the rooms and people grouped by fk_rooms by selecting three columns: they are RoomsID, ​​RoomName, People, and in the second - the connection of rooms and the Item grouped by fk_rooms, selecting three columns, they are RoomID, RoomName, Things. In your request, you name these choices as t1 and t2 and attach t1 and t2 to RoomID, select t1.RoomName, t1.People, t2.Things.

Good luck.