[Solved] Check availability and list available rooms [closed]

by


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

2

solved Check availability and list available rooms [closed]