Check if date range matches another date range in sql

Question

I have a table in which room orders are stored, diagram:

ID | ROOM_ID | CHECK_IN_DATE | CHECK_OUT_DATE | USER_ID

I need to run a search query for rooms that are available / inaccessible between the set date range.

Also keep in mind that there is another table that contains the dates when the room was previously reserved, and its format:

ROOM_ID | DATE

SO I need to run a query that searches for available rooms within a given range. How would I formulate a request? I am using MySQL here.

--- edit ---

Theres also a table of rooms schemes:

ID | ROOM DETAILS 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'
   )

, , . ?

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

5 :

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

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

s = start/e = end firste line - ,

- , , : search.end < entry.start search.start > entry.end