4

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
  • 2
    You may want to add the table definitions, as well as describe the expected result and the current result.
    – mustaccio
    Apr 21, 2014 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. Apr 22, 2014 at 16:49

2 Answers 2

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.

2
  • 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 Apr 22, 2014 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)... Feb 21, 2015 at 0:11
0

For the tables, I guess "room_type" actually means "room" and plan means avaliable room in a particular date?

For the result, not clear what you want, guess it is about counting of rooms

So I guess it is what you want:

Select count(s.room_name) num_room, s.max_adults, s.max_children
FROM
(
   Select r.room_name, r.max_adults, r.max_children
   FROM property p
   INNER JOIN room_type r ON p.property_ID=r.property_ID
   INNER JOIN plan pl ON pl.room_type_ID=r.room_type_ID
   WHERE (r.max_adults >= 3 AND r.max_children >= 0)
   AND (pl.day >= "2014-07-07" AND pl.day <= "2014-07-11")
   GROUP BY r.room_name, r.max_adults, r.max_children
   HAVING count(pl.day)= 4
) s
GROUP BY s.max_adults, s.max_children

And result will be something like

num_room  max_adults  max_children
11        2           0 
2         4           2
4         2           1

If not, please provide simple data and result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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