select - MySQL Left Join Many to One Row -

to simplify problem: let's 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 like:

select r.name room_name, p.name name, t.name thing rooms r  left join people p on p.fk_rooms = r.id left join things t on t.fk_rooms = r.id 

which in case works except few have many 1 relationship "rooms" table. instead of new rows in result set holding different names "people" , "things" in relation "rooms" table, receive 2 rows:

1. kitchen, john, andy, laura, sink 2. bedroom, mary, tv, bed 

a group by on r.id select 1 row each table. highly appreciated!

here you're looking for:

select r.name room_name,     group_concat(p.name separator ',') people_name,     group_concat(t.name separator ',') things rooms r  left join people p on p.fk_rooms = r.id left join things t on t.fk_rooms = r.id group r.id