mysql - 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.

Answer 1

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