2

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per hotel like this:

  • 1 Room with 2 Adults / 0 Children
  • 1 Room with 4 Adults / 2 Children
  • 1 Room with 2 Adults / 1 Children

Here is my query:

SELECT 
COUNT(pl.day) AS Days, p.property_ID AS Hotel_ID, 
p.name AS Hotel_Name, r.room_name AS Room_Name, r.room_type_ID AS Room_ID

FROM property p

  INNER JOIN room_type r 
    ON p.property_ID=r.property_ID
    AND (r.max_adults >= 3 AND r.max_children >= 0)

  INNER JOIN plan pl 
    ON pl.room_type_ID=r.room_type_ID
    AND (pl.day >= "2014-07-07" AND pl.day <= "2014-07-11")

GROUP BY Room_ID, Hotel_ID
HAVING Days = 4

EDIT: The tables are:

  • property > property_ID ...
  • plan > property_ID | room_type_ID | day | rate | alloc | min_stay ...
  • room_type > property_ID | max_adults | max_children ...
  • ...
  • 2
    You may want to add the table definitions, as well as describe the expected result and the current result. – mustaccio Apr 21 '14 at 19:18
  • Edited my post. The expected result should be Rooms that match number of rooms given with additional Adults/Children per hotel. Right now it shows anything that matches regardless if there are less rooms than asked. – user1906941 Apr 22 '14 at 16:49
0

first insert the resultset of the room_type table to a temp table with the filtering by your parameters

 r.max_adults >= 3 AND r.max_children >= 0

then query that table and join the property and plan tables to it and it should be faster.

  • I have multiple parameters to filter per room though. r.max_adults >= 3 AND r.max_children >= 0 , r.max_adults >= 2 AND r.max_children >= 1 , r.max_adults >= 2 AND r.max_children >= 2 – user1906941 Apr 22 '14 at 16:52
  • 1
    Can be (r.max_adults >= 3 AND r.max_children >= 0) OR (r.max_adults >= 2 AND r.max_children >= 1)... – Thomas Cleberg Feb 21 '15 at 0:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.