mysql - hotel reservation system SQL query: identify when one specific room is available

Question

I have a query for a hotel reservation system and I need to find dates when a specific room is available. (It's a boutique hotel where people reserve specific rooms, so they know the exact room they want before they get to this code. The result I'm after is the detail for ONE room, the one I specify in the query-- I am not looking for info on multiple rooms.)

The 'availability' table schema is simple, each row is:

room_id
date_occupied - a single day that is occupied (like '2011-01-01')

So, if, for example, a room is occupied from January 1 to January 5, five rows are added to the availability table, one for each day the room is occupied.

Here's what I'm trying to work out:

• the query to find when a specific room is available between a start and end date, sort of like:

SELECT rooms.* FROM rooms, availability
 WHERE rooms.id = 123
   AND availability.room_id = rooms.id
   AND nothing between start_date and end_date is in availability.date_occupied

• I'm also seeking a similar query where I just want to see if a specific room is available for the start date and the following two days, something like:

SELECT rooms.* FROM rooms, availability
 WHERE rooms.id = 123
   AND availability.room_id = rooms.id
   AND start_date, start_date+1day and start_date+2days is not in availability.date_occupied

I'm a bit stuck trying to figure out the exact joins. Any suggestions? Note that if it helps, I'm totally open to a different schema for the availability table. Whatever makes the queries work most efficiently.

Answer 1

I think the best way to do this is to simply run a query to see if a room is occupied and indicate availability in the event that no rows are returned.

As such, for you first query:

SELECT COUNT(id) AS occupied_days FROM availability
WHERE room_id = 123
      AND date_occupied BETWEEN @start_date AND @end_date;

If occupied_days == 0 then the room is available.

For your second query, just replace @end_date with DATE_ADD(@start_date, @num_days)

Just some comments on answers involving a join: since you're limiting your search to a specific room_id, it makes no sense to join the availability table to the room table, since it's providing no necessary information. All these LEFT JOINs are just complicating the query, potentially impairing performance and providing nothing of any use.

Also, while you may baulk at the approach of searching for occupancy and then inferring availability, I would guess that this query is by far the fastest and easiest for the query engine to optimize, since it's based on a single column which, if indexed, will make things even faster.