Merging data from another table column into 1 seperated column

Question

I've got 3 tables (tbl_reservations / tbl_series / tbl_rooms), how can i return a with the matching rooms merged into 1 column separated by a space or a "|", along with data from the reservation table?

tbl_reservations
------------
id, startdate, enddate, series
1, 2014-05-20, 2014-05-22, 1
2, 2014-05-24, 2014-05-25, 2

tbl_series
--------
reservation, room
1, 1
1, 3
1, 4
2, 1
2, 2

tbl_rooms
-----
id, name
1, room a
2, room b
3, room c
4, room d

When i need returning is something like this...

startdate, enddate, rooms
2014-05-20, 2014-05-22, 1|3|4
2014-05-24, 2014-05-25, 1|2

Answer 1

as mentionned, use GROUP_CONCAT. You don't seem to need tbl_rooms by the way...

select r.id, 
       r.startdate, 
       r.enddate, 
       GROUP_CONCAT(s.room order by s.room separator '|' ) as rooms
from tbl_reservations r
 join tbl_series s on s.reservation = r.series
group by r.id, r.startdate, r.enddate

see SqlFiddle

Answer 2

do you want to have all this in one column? in which case this is a duplicate:

MySQL, Concatenate two columns

or

MySQL combine two columns and add into a new column

or do you want to have a table after the query which will have each individual reservation in its own row. in which case you need to look at using the

INNER JOIN

functionality something like this:

Select * from tbl_rooms t1 
inner join 
(select * from tbl_reservations st1 
    inner join 
    (select * from tblseries) st2 
    on st1.id=st2.reservation
) t2 on t1.id = t2.room