问题描述
我创建一个房间预订系统。
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).
这篇关于选择可用房间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!