Logic is actually pretty simple here, you don’t want anything where the start and end are inside your period, or where they’re at opposite sides of any point in the range you’re looking for.
SELECT R.roomID, R.name, R.facilities FROM -- Use the table rooms, but name it R for shorthand. Rooms R -- Left joins return null if there's no match, which we use in the where clause to identify these rows LEFT JOIN Bookings B -- B is bookings, R is rooms, they're aliased to make things easier ON B.RoomId = R.RoomId AND ( -- As I said at the top of this answer, we want to exclude anything that matches any of 3 conditions: -- The start date is between the date's the room is already booked for @SearchCheckingStart BETWEEN B.startDate AND B.endDate OR -- The end date is in the period where the room is already booked @SearchCheckingEnd BETWEEN B.startDate AND B.endDate OR -- Or our date range lies between the period we're booking for B.startDate BETWEEN @SearchCheckingStart AND @SearchCheckingEnd ) WHERE -- We're only interested in results where MySQL couldn't match the results earlier, this gives us the rooms that are free. B.RoomId IS NULL
solved Check availability and list available rooms [closed]