MySQL grouping syntax

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:

Hotel
   Room x
   Room y
   Room z

(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!
LVL 2
erzoolanderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
No points for this because it's not an answer, but why not just run a few different queries?  It's not like the number of hotels in the world is a problem for modern data bases - there are a lot, but any decent web host server will be fast enough that a handful of queries can be used to build a web page without seeing any noticeable delay.
erzoolanderAuthor Commented:
I was kinda hoping for a "One query to rule them all" solution...lol

But - I suppose I could first do a query based upon rates/dates/etc - and from that build the list of relevant hotels into an array.  Then run a secondary query on the hotels themselves right-joining the rooms/rates and only return the results for each hotel if the rooms meet the specifics.  

Sound like a decent solution?
Ray PaseurCommented:
Yes, that sounds like something I've done many times.  You can create a "down-select" into a smaller temporary table (use ENGINE=MEMORY for best performance).  Then your queries against the temporary table will be lightning fast!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.