Need to see if a range of dates overlaps another range of dates in sql

Question

I have a table which stores bookings of rooms, the schema is:

ID | ROOM_ID | CHECK_IN_DATE | CHECK_OUT_DATE | USER_ID

I need to run a search query for rooms which are available/unavailable between a set range of dates.

Also keep in mind that there exists another table which holds dates when the room is prebooked and its in the format:

ROOM_ID | DATE

SO I need to run a query which looks for rooms available within a set range, How would I formulate the query? I'm using MySQL here.

---edit---

Theres also a Rooms table of the schema:

ID | ROOM DETAILS etc

The unavailability/prebooked dates table basically holds sporadic single dates, each date in the unavailability table refers to a date when the room for some reason cannot be booked eg: maintenance etc

Answer 1

SELECT
   ROOM_ID
FROM
   Rooms r
   LEFT JOIN Bookings b ON (
      r.ROOM_ID = b.ROOM_ID
      AND b.CHECK_IN_DATE > '$MAX_DATE'
      AND b.CHECK_OUT_DATE < '$MIN_DATE'
   )

I'm not sure how pre-booked rooms factors in as there is no date range. Do pre-booked rooms also get an entry on bookings or not?

Answer 2

SELECT id FROM rooms WHERE id NOT IN (
   SELECT room_id FROM bookings 
   WHERE check_in_date < 'end date' AND check_out_date > 'start date'
);

Answer 3

there are like 5 possible ways:

---s-----e---

s-e----------
--s-e--------
-----s-e-e---
--------s-e--
----------s-e

s = start / e=end firste line is your database set and the other are possible searches

only the first and last one are the ones that you want so what you look for is: search.end < entry.start OR search.start > entry.end