Hi guys I have a table which holds dates when a room is unavailable in the schema:
ROOM_ID | DATE_UNAVAILABLE
I need a sql query that checks if a room is available during between a range of two dates - some thing along the line of
Select All rooms that are constantly available between date 1 and date 2
or rather
select all rooms that don not have a date entered in the date unavailable table which falls between date 1 and date 2
I'm using php MySQL here
Thanks
解决方案
The inner query finds the room that are not available, then we use a Not-exists left join to remove those dates from our results, leaving us with available rooms only.
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
Alternativly, if you have correct indexes in place, skipping the group may 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