Room Booking Query

Question

I have a problem in writing the sql to get the availables rooms from the tables.

My tables structures are given below.

Table Booking
ID | START_DATE | END_DATE


Table BookingRoom (Intermediate Table)
ID   | BOOKING_ID   |ROOM_ID

A Room can be linked to many bookings and a booking can contain many rooms

Table Room
Contains the ID room

I have Tried This but there is a probleme if a room is linked to 2 differents bookings in separate dates the comparaison will be maked with only the first booking id

SELECT DISTINCT r.ID FROM room AS r ,booking AS b,bookingroom AS br
 WHERE r.ID = br.ID_ROOM
 AND b.ID = br.ID_BOOKING
 AND (
           b.END_DATE < '05/14/2013'
        OR b.START_DATE > '05/15/2013'
     )

Can someone help me to write the SQL to get the available rooms between a checkin and checkout date.

Are you looking for rooms at a specific date/time or during a period? — Chief Wiggum, May 15 '13 at 00:00
In fact i'm searching for availables rooms between a check in date and a checkout date — Safwen, May 15 '13 at 00:08

score 2 · Accepted Answer · answered May 15 '13 at 01:16

If all you want is the list of rooms available for the entire range of desired dates, then something like the following might work:

Select Room.Id
From Room
Where Room.Id Not In    (
                        Select RoomId
                        From BookingRoom
                            Join Booking
                                On Booking.Id = BookingRoom.BookingId
                        Where Booking.StartDate <= 'DesiredEndDate'
                            And Booking.EndDate >= 'DesiredStartDate'
                        )
Order By Room.Id

So, using the original example, we might get:

Select Room.Id
From Room
Where Room.Id Not In    (
                        Select RoomId
                        From BookingRoom
                            Join Booking
                                On Booking.Id = BookingRoom.BookingId
                        Where Booking.StartDate <= '2013-05-15'
                            And Booking.EndDate >= '2013-05-14'
                        )
Order By Room.Id

DougM · Answer 2 · 2013-05-15T00:20:13.463

You'll want to define a clear recordsets for "all bookings made on day X", and then use an outer join to retrieve those rooms that don't have a booking for said day.

SET @myDate = #someDay#
SELECT R.ID
FROM Room R
WHERE R.ID NOT IN (
    SELECT BR.ROOM_ID
    FROM BookingRoom BR
    INNER JOIN Booking B ON BR.Booking_ID = B.ID
    WHERE @myDate BETWEEN B.start_date and B.end_date
)

Edit:

Since you want ALL open rooms during a reservation, you'll need to do a more complicated query. I'll assume that you already have the dates in question loaded outside, since doing a date-based many-to-many query is a PITA.

SET @dateStart = #Start#
SET @dateEnd = #End#

SELECT R.ID
FROM Room R
WHERE R.ID NOT IN (
    SELECT BR.ROOM_ID
    FROM BookingRoom BR
    INNER JOIN Booking B ON BR.Booking_ID = B.ID
    WHERE B.start_date BETWEEN @dateStart AND @dateEnd
    AND B.end_date BETWEEN @dateStart AND @dateEnd
)

Room Booking Query

2 Answers2

Linked