Room booking sql query

Question

I have a problem in writing the sql to get the available rooms from the tables. my table structures are given below.

table : booking

booking_id    |    room_id   |    start_datetime  |   end_datetime    |   customer_id
-------------------------------------------------------------------------------------
001           |     1        |  12-09-2012 2:35pm | 14-09-2012 9:00am |    23
002           |     2        | 10-09-2012 1:00am  | 20-09-2012 9:00am |    20
003           |     4        |  09-09-2012 1:35pm | 21-09-2012 9:00am |    43
004           |     1        |  22-09-2012 2:35pm | 24-09-2012 9:00am |    9
005           |     3        |  12-09-2012 9:00am | 13-09-2012 9:00am |    53
006           |     6        |  15-09-2012 9:00am | 19-09-2012 9:00am |    27

Table : rooms

contains the details about the rooms and the primary key of the table is room_id and it has 10 rooms from 1-10.

My problem is I want to know the rooms are available between 14-09-2012 6:00pm to 21-09-2012 9:00am which means I should only get the results of the room_id's as 1,3,5,7,8,9,10.

Can someone help me to write the SQL to get the available rooms from the above table structures. I'm using mysql as the database engine. Thanks in advance.

Answer 1

This should do it; if there is a reservation that does not end before or start after the reservation we want, the room is considered busy.

SELECT r.room_id
FROM rooms r
WHERE r.room_id NOT IN (
    SELECT b.room_id FROM bookings b
    WHERE NOT (b.end_datetime   < '2012-09-14T18:00'
               OR
               b.start_datetime > '2012-09-21T09:00'))
ORDER BY r.room_id;

SQLFiddle here.