检查可用性并列出可用房间[关闭]

Question

These days I am working hard for my degree project on Hotel Room Reservation System.
Now I strucked at the check availability stage.
Here is my table structure

Bookings

`ID` int(11) 
`roomID` int(11)
`startDate` date 
`endDate` date

Rooms table

`roomID`    int(11)              
`name`  varchar(255)                     
`facilities`  mediumtext

Imagine there are 5 rooms. roomIDs are 01,02,03,04 and 05. amoung these rooms, 01 is booked. checkin date is 05-11-2013 & checkout date is 10-11-2013.

And also roomID 04, booked in 08-11-2013 to 09-11-2013

If user check availability from 04-11-2013 to 10-11-2013, only room ID 02,03 and 05 should display.

How can I build a system to display result?

Can someone help me to build sql query to check availability?

Answer 1

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