Select available rooms in given date range

Question

I am building a hotel booking system and I just got stuck when trying to make a query that retrieves all available room types in a given range.

I have two tables Rooms and Reservations. The Rooms table hold the rooms in the hotel. Their number (id) and their type (type).

Table Reservations holds the reservations made by the clients. The reservation number (id), the associated room number (room_id), and date range (from and to)

I have tried this query:

SELECT room_id as available_room_number, type

FROM roomstesting

LEFT JOIN reservations ON roomstesting.id = reservations.room_id
WHERE reservations.id 
  NOT IN (reservations.e_from <='"2014-03-07 19:00:00' 
           AND reservations.e_to >='2014-03-08 19:00:00')

I was trying to get all the available room types in the range from March 7 to March 8. I was expecting to get the modern room as result of the query. Because modern room id 4 has no reservations made overlapping with the date range and all the other 3 rooms all have reservations made from March 6 to March 9. But I am not getting the result I want. Below is the structure of my database (simplified)

Rooms

| id | type         |
|||||||||||||||||||||
|  1 | master suite |
|  2 | master suite |
|  3 | modern room  |
|  4 | modern room  |

Reservations

| id | room_id | from                | to                  |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|  1 |    1    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |
|  2 |    2    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |
|  3 |    3    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |

Expected result

| available_room_number | type       |
||||||||||||||||||||||||||||||||||||||
|          4            | modern room|

If anyone here can tell me how I should approach this that would be perfect. Looking forward to your replies.

Answer 1

Try this:

SELECT * FROM Rooms WHERE ID NOT IN(SELECT room_id FROM reservations WHERE '2014-03-07 19:00:00' < e_to AND '2014-03-08 19:00:00' > e_from)