Combine multiple room availability queries into one

You would need to construct a query to group on the room ID and then check that there is availability on each date, which can be done using the having clause. Leaving the where clause predicate in for room_availability_date will help to keep the query efficient (as indexes etc. can't be used with a having clause easily).

SELECT 
    room_availability_rid

WHERE room_availability_date IN ('2011-02-13','2011-02-14','2011-02-15')
  AND room_availability_number > 0

GROUP BY room_availability_rid 

HAVING count(case room_availability_date when '2011-02-13' THEN 1 END) > 0
   AND count(case room_availability_date when '2011-02-14' THEN 1 END) > 0
   AND count(case room_availability_date when '2011-02-15' THEN 1 END) > 0

You can group by room ID, generate a list of dates available, and then see if all the dates you need are included.

This will give you a list of dates each room is available:

select `room_availability_rid`,group_concat(`room_ availability_date`) as `datelist`
       from `table` where room_availability_number>0 
       group by `room_availability_rid`

Then we can add a having clause to get the rooms that are available on all of the dates we need:

select `room_availability_rid`,group_concat(`room_ availability_date`) as `datelist`
        from `table` where room_availability_number>0
        group by `room_availability_rid`
        having find_in_set('2011-02-13',`datelist`) and
               find_in_set('2011-02-14',`datelist`) and
               find_in_set('2011-02-15',`datelist`)

This should work. Test it for me will ya? :)

I think I can improve on a'r's answer:

SELECT 
    room_availability_rid, count(*) n

WHERE room_availability_date IN ('2011-02-13','2011-02-14','2011-02-15')
  AND room_availability_number > 0

GROUP BY room_availability_rid 

HAVING n=3

Edit: This of course assumes that there is only one table entry per room per day. Is this a valid assumption?