Geek asks and answers

How to efficiently find all records that DO NOT overlap with a date interval?

Say I have a hotel_rooms table, each of which has a number of related booking entries.

The tables look like this:

hotel_rooms:

hotel_room_id | hotel_name
--------------------------
1 | Hotel a
2 | Hotel B
3 | Hotel C

Reservation:

reservation_id | hotel_room_id | start_date | end_date
-------------------------------------------------- ----------
1 | 1 | June 1, 2011 | June 10, 2011
2 | 1 | June 20, 2011 | June 30, 2011
3 | 2 | June 11, 2011 | June 15, 2011
4 | 3 | June 1, 2011 | June 4, 2011



hotel_rooms checkin_date checkout_date, - hotel_rooms?

, checkin_date 4 2011 , checkout_date - 8 2011 , Hotel A Hotel C , B. checkin_date 16 2011 , checkout_date 19 2011 .

, , , , , . ?

MySQL PHP ( , ).

+3
2
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


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
  )
+3

, NOT EXISTS . , .

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)
0

More articles:


All Articles