Let us say I've got a table of hotel_rooms, each with numerous connected bookings records.

The tables would seem like this:

hotel_rooms:


hotel_room_id  hotel_title

--------------------------

1              Hotel A

2              Hotel B

3              Hotel C

bookings:


reservation_id  hotel_room_id  start_date    finish_date

------------------------------------------------------------

1               1              1 June 2011   10 June 2011

2               1              20 June 2011  30 June 2011

3               2              11 June 2011  15 June 2011

4               3              1 June 2011   4 June 2011



How do i find all of the hotel_rooms having a checkin_date and checkout_date that does not overlap with the hotel_rooms' bookings?

For instance, when the checkin_date was 4 June 2011 and checkout_date was 8 June 2011, then Hotel A and Hotel C wouldn't display in the results, only Hotel B. When the checkin_date was 16 June 2011 and also the checkout_date was 19 June 2011, the 3 hotels would appear.

I am sure you will find a number of ways to carry out this, however for our situation, let us say you will find lots of records in hotel_rooms and every room is connected with lots of bookings records. An amount be a competent way additional search?

For my reasons, I am using MySQL and PHP (however i don't always require a solution focused either to).

SELECT h.*
FROM hotel_rooms h
  LEFT JOIN reservations r
    ON h.hotel_room_id = r.hotel_room_id
   AND checkin_date <= r.end_date
   AND checkout_date >= r.start_date
WHERE r.reservation_id IS NULL

or:

SELECT *
FROM hotel_rooms h
  WHERE NOT EXISTS (
    SELECT *
    FROM reservations r
    WHERE h.hotel_room_id = r.hotel_room_id
      AND checkin_date <= r.end_date
      AND checkout_date >= r.start_date
  )

I believe a few NOT Is available subqueries would meet your needs. The timing should not be any problem either, presuming the tables are correctly indexed.

SELECT *
FROM hotel_rooms
WHERE NOT EXISTS (SELECT 1 FROM reservations 
                  WHERE reservations.hotel_room_id = hotel_rooms.hotel_room_id
                  AND checkin_date BETWEEN start_date AND end_date)
AND NOT EXISTS (SELECT 1 FROM reservations 
                WHERE reservations.hotel_room_id = hotel_rooms.hotel_room_id 
                AND checkout_date BETWEEN start_date AND end_date)