SQL To check if a date set falls within a specified date range

Question

Hi guys I have a table that contains dates when the room is not available in the diagram:

ROOM_ID | DATE_UNAVAILABLE

I need a sql query that checks if a room is available in the range between two dates - something along the line

Select All rooms that are constantly available between date 1 and date 2

or rather

select all numbers on which the date entered in the unavailability table, which is between date 1 and date 2, is not indicated

I am using php mysql here

thank

Answer 1

An internal query finds a room that is not available, then we use a non-existent left join to remove these dates from our results, leaving us only in accessible rooms.

SELECT r.ROOM_ID
FROM rooms r LEFT JOIN (
    SELECT ROOM_ID
    FROM tableName
    WHERE DATE_UNAVAILABLE BETWEEN 'Date1' AND 'Date2'
    GROUP BY ROOM_ID
  ) g ON r.ROOM_ID = g.ROOM_ID
WHERE g.ROOM_ID IS NULL

Alternatively, if you have the correct indexes in place, skipping a group might be faster:

SELECT r.ROOM_ID
FROM rooms r LEFT JOIN tableName g ON r.ROOM_ID = g.ROOM_ID
    AND g.DATE_UNAVAILABLE BETWEEN 'Date1' AND 'Date2'
WHERE g.ROOM_ID IS NULL

Answer 2

, rooms, :

select * from rooms r where not exists
(select * from room_unavail u
    where r.id = u.id and u.date_unavailable between date1 and date2)

, , , , .

Answer 3

Let myRoomId be the identifier of this room, and dateStart and dateEnd be the start and end dates of the time interval. Than:

select count(*) from tblRooms
where date_unavailable>=dateStart and date_unavailable<=dateEnd
and room_id = myRoomId

this select returns 0 if room is available.