Query multiple rooms with different conditionals (Adults, Children) 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 &gt;= 3 AND r.max_children &gt;= 0)

  INNER JOIN plan pl 
    ON pl.room_type_ID=r.room_type_ID
    AND (pl.day &gt;= "2014-07-07" AND pl.day &lt;= "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 ...
...
first insert the resultset of the room_type table to a temp table with the filtering by your parameters

 r.max_adults &gt;= 3 AND r.max_children &gt;= 0


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