Room Booking Sql Query

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_datetim

Solution 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 NOTIN (
    SELECT b.room_id FROM bookings b
    WHERENOT (b.end_datetime   < '2012-09-14T18:00'OR
               b.start_datetime > '2012-09-21T09:00'))ORDERBY r.room_id;

SQLFiddle here.