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!

score 8 · Accepted Answer · answered May 21 '13 at 23:26

8

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

answered May 21 '13 at 23:26

medina

8,051
4
25
24

1

Thanks! Got it to work with some modification as the real query was quite complex. – NewInTheBusiness May 22 '13 at 05:17
1

I've spent hours with this just because I forgot to group. Thanks. – karliwson Aug 08 '19 at 17:50

Lajos Arpad · Answer 2 · 2013-05-21T23:25:37.083

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

Maybe you could use two different queries: The first will result the join of Rooms and People, grouped by fk_rooms, having selected three columns, they are being RoomsID, RoomName, People, while the second will result the join of Rooms and Thing, grouped by fk_rooms, having selected three columns, they are being RoomID, RoomName, Things. In your query you name these selections as t1 and t2 and join t1 and t2 by RoomsID, select t1.RoomName, t1.People, t2.Things.

Good luck.

MySQL Left Join Many to One Row

2 Answers2

Linked