Request room reservation in sql

Question

I am having trouble writing sql to get available rooms from 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 information about the rooms, and the primary key of the table is room_id, and it has 10 rooms from 1 to 10.

My problem is that I want to know that the numbers are available between 09/14/2012 18:00 and 09/21/2012 9:00, which means that I should get room_id results as 1,3,5, 7 8,9,10.

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

Answer 1

That should do it; if there is a reservation that does not end earlier or begins after the reservation we want, the room is considered occupied.

 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 .