hotel reservation system SQL: identify any room available in date range

Question

(In case this seems familiar: I asked a different question similar to this one, but I just got a note that the site design has changed and now the owners do want a way to search for any room that is available between a range of dates. So this is a new question...)

I'm working on a hotel reservation system and I need to find which rooms are available between a range of dates. The existing 'availability' table schema is simple, the columns are:

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

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

I'm trying to figure out the query to find what rooms are available between a start and end date (while also filtering on some other parameters like non-smoking, bed size, etc.)... in pseudo-SQL it would be sort of like:

SELECT (a list of room IDs) FROM rooms, availability
 WHERE rooms.nonsmoking = 1
   AND rooms.singlebed = 1
   AND nothing between start_date and end_date is in availability.date_occupied

As with my other question, I'm a bit stuck trying to determine the exact query and how to join the tables it in the most efficient way. Thanks for the help.

Answer 1

If I understood your db structure properly, you need to find a row in rooms with no corresponding rows in availability.

SELECT r.* 
FROM rooms r
  LEFT JOIN availability a ON (r.id = a.room_id 
 AND a.date_occupied BETWEEN :start_date AND :end_date)
WHERE a.id IS NULL