Select available rooms

Question

I am creating a room booking system.

I need to create a SQL query so that once a user has selected a date, a list of rooms that have are available on a specific day for a specific time are displayed. But I only store the rooms that are booked.

The 3 tables included in this at the moment are Tbl_Room, Tbl_Booking and Tbl_TimeSlot

Tbl_Room stores all the different rooms.

Tbl_Booking stores the date booked, RoomID, TimeslotID, BookingName

Tbl_TimeSlot contains StartTime and EndTime

So, I want to display each time slot that is available for a selected day and a selected time. The SQL query I have tried is:

SELECT
    Tbl_TimeSlot.TimeslotID_PK, 
    Tbl_TimeSlot.StartTime, 
    Tbl_TimeSlot.EndTime,
    Tbl_Booking.RoomID_FK, 
    Tbl_Booking.BookingName, 
    Tbl_Booking.BookingDate,  
    Tbl_Room.RoomName         
FROM
    Tbl_Room RIGHT 
    OUTER JOIN
        Tbl_Booking ON Tbl_Room.RoomID_PK = Tbl_Booking.RoomID_FK 
    RIGHT OUTER JOIN
        Tbl_TimeSlot ON Tbl_Booking.TimeSlotID_FK = Tbl_TimeSlot.TimeslotID_PK
WHERE (Tbl_Booking.BookingDate <> @BookingDate)
    AND (Tbl_Booking.RoomID_FK <> @RoomID)

But nothing is appearing.

Any suggestions?

score 0 · Answer 1 · answered Feb 13 '13 at 13:41

Since you need the available rooms for a given period, I think you should have 2 input parameters: @FromBookingDate and @ToBookingDate.

Then, you can query the database for rooms that are occupied in that period.

After having the list of occupied rooms, you simply assume that the other rooms are available.

Example to get the occupied rooms:

select b.RoomId from Tbl_Booking b
inner join Tbl_TimeSlot s on b.RoomId = s.RoomId
where s.StartTime <= @ToBookingDate and s.EndTime > @FromBookingDate

To get the available rooms:

select * from Tbl_Room where id not in ( ...<occupied rooms>... )

score 0 · Answer 2 · answered Feb 13 '13 at 13:45

You want an outer join of every room and then filter for the rooms which have no booking. That is, using an outer join, the records where the other table is NULL.

SELECT ...
FROM Tbl_Room
  LEFT OUTER JOIN Tbl_Booking ON Tbl_Room.RoomID_PK = Tbl_Booking.RoomID_FK 
WHERE Tbl_Booking.BookingDate = @BookingDate
  AND Tbl_Booking.RoomID_FK IS NULL

Jonas · Accepted Answer · 2013-02-13T19:35:50.343

0

multiply Tbl_Room with Tbl_TimeSlot using CROSS JOIN (produced: room x timeslot for 1 day) then subtract with room x timeslot which have been booked @BookingDate , like this:

 SELECT Tbl_Room.RoomName, Tbl_TimeSlot.StartTime
 FROM Tbl_TimeSlot CROSS JOIN Tbl_Room
 EXCEPT
 (SELECT Tbl_Room.RoomName, Tbl_TimeSlot.StartTime
  FROM Tbl_TimeSlot CROSS JOIN Tbl_Room 
    FULL OUTER JOIN Tbl_Booking 
        ON Tbl_TimeSlot.TimeslotID_PK = Tbl_Booking.TimeSlotID_FK
 WHERE (Tbl_Booking.BookingDate = @BookingDate))

that query produced empty room & empty slot time at given date (@BookingDate).

edited Feb 13 '13 at 19:35

answered Feb 13 '13 at 17:01

Jonas

76
3

I have just noticed, say if the 9:00 slot was booked for room 1 on a specific day, that slot is not showing on the results which is correct. However, it is also not showing for room 2, although that room has not been booked for that time on that date. Do you have any suggestions what has gone wrong? Thanks – Claire Feb 19 '13 at 15:40

Select available rooms

3 Answers3

Linked