Combine multiple room availability requests into one

Question

I am currently trying to optimize a database by combining queries. But I continue the dead end by optimizing the room availability request.

I have a room availability table, where in each record the number of rooms per day is indicated. It is formatted like this:

• room_availability_id (PK)
• room_availability_rid (fk_room_id)
• room_availability_date (2011-02-11)
• room_availability_number (number of rooms available)

The problem is getting a list of rooms that are available for EVERY given day. When I use IN () as follows:

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

If the 14th number has an availability of 0, it still gives me two more dates. But I only want this room_id when it is available for ALL three dates.

Please tell me that there is a way to do this in MySQL, except to request each combination of date / room / availability separately (here's what is being done now :-()

I tried all kinds of combinations, tried to use room_availability_date = ALL (...), tried some dirty duplicate subqueries, but to no avail.

Thank you in advance for any thoughts!

Answer 1

I think I can improve a'r 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 entry per day in a room. Is this a valid assumption?

Answer 2

You will need to create a request for grouping by room ID, and then check availability for each date, which can be done using the having clause. Exiting the where where predicate for room_availability_date will help maintain the effectiveness of the request (since indexes, etc. cannot be easily used with the condition of availability).

 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 

Answer 3

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

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

 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 sentence to get rooms that are available for all 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`) 

That should work. Will it be tested for me? :)