选择可用房间 [英] Select available rooms

问题描述

我创建一个房间预订系统。

I am creating a room booking system.

我需要创建SQL查询，以便一旦用户已经选择了日期，房间具有可在特定时间的特定一天所显示的列表。但我只存储被预定房间。

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.

目前包括在这个三表是 Tbl_Room ， Tbl_Booking 和 Tbl_TimeSlot

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

Tbl_Room 存储所有不同的房间。

Tbl_Room stores all the different rooms.

Tbl_Booking 存储预订的日期，RoomID，TimeslotID，BookingName

Tbl_Booking stores the date booked, RoomID, TimeslotID, BookingName

Tbl_TimeSlot 包含开始时间和结束时间

Tbl_TimeSlot contains StartTime and EndTime

所以，我想显示每个时隙可用为选定日期和选定的时间。
SQL查询我曾尝试是：

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.

有什么建议？

推荐答案

乘Tbl_Room与Tbl_TimeSlot使用CROSS JOIN（制作：1天房X时隙），然后与已经被预订一空@BookingDate，这样的房X时隙减：

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))

这是查询产生空室及放大器;空槽时间给定的日期（@BookingDate）。

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

这篇关于选择可用房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！