I've got a database of hotels.
In it, there are three tables I'm concerned with..
Table 1 = Generic Hotel Data
Table 2 = Room Data (associated with Table 1 by field hotelid)
Table 3 = Rate Data (associated with Table 2 by field roomid) which includes date ranges (all in the same row)
I'd like to do a query where the results end up displayed like:
(in other words - rooms are grouped by the hotel logically - but hotel is only displayed once - but resulting rooms are iterated out).
Is there a single query I can run that will output that result? Doing a:
Select * from rates t1
left join rooms t2 on t2.roomid = t1.roomid
left join hotels t3 on t3.hotelid = t2.hotelid
WHERE t1.date < xxxxx
clearly isn't it - but is along the lines I'm thinking.
Any suggestions? Or will I need to grab a list of the dates first - store the hotels in an array or something - and then iterate through that instead? I'd rather not go that route though...!
Any help is appreciated!