SQL 通过日期检查选择可用房间 [英] SQL select the available rooms by date cheking

问题描述

我的数据库中有一个名为 rooms 的表，其中包含房间信息和属性，另一个表名为预订表，其中包含 Room Reserved、FromDate 和 ToDate.

I have in my database a table called rooms that contain the rooms information and property ,and another table called reservation table that contain the Room Reserved, FromDate and ToDate .

我想要做的是让用户选择他想要预订的房间大小并选择预订房间的日期，然后我为他提供可用房间，取决于房间预订表.

What i want to do is to make the user pick room size that he want to reserve and pick the date for reserving the room ,then i provide for him the available rooms depend on the Room Reservation table.

这是我所做的:

SELECT  * FROM Rooms,Reservations WHERE 
Rooms.R_Size = 'roomSize' AND ('4/19/2013' NOT 
BETWEEN Reservation.FromDate AND Reservation.ToDate  AND '4/19/2013'
NOT BETWEEN Reservation.FromDate AND Reservation.ToDate) 

问题是返回给我重复的房间，即使它在特定预订的预订日期之间，但不在另一个预订的预订日期之间，它仍然会退还给我.

The problem its return to me duplicate's rooms and even if its between the reserved date in specific reservation but its not between reserved date in another reservation still it will return it to me.

我想要的是检查房间是在同一日期或在特定日期之间预订的，如果是，我根本不希望它被选中并返回.

What i want is to check if the room is reserved at the same or between a specif date and if it is i don't want it to be selected and returned at all.

谢谢.. 抱歉我的英语不好

Thanks.. and sorry for my poor english

推荐答案

您的查询有两个问题.一是房间和预订之间的连接没有条件，这样每次满足日期测试的预订将返回一次正确大小的房间.另一个问题是您的日期测试是错误的，因为它不会检测完全在新​​预订的日期间隔内的现有预订.

There are two problems with your query. One is that there is no condition on the join between rooms and reservations, such that rooms of the correct size will be returned once for each reservation satisfying the date tests. Another problem is that your date test is wrong as it will not detect existing reservations that is completely within the date interval of the new reservation.

像这样的查询应该会给你想要的结果:

A query like this one should give you the result you want:

SELECT * FROM Rooms
LEFT JOIN Reservations 
ON Reservations.R_Number = Rooms.Number
AND Reservations.ToDate > '4/19/2013'
AND Reservations.FromDate < '4/20/2013'
WHERE Rooms.R_Size = 'roomSize' 
AND Reservations.R_Number IS NULL 

它的工作原理是将房间加入该房间的预订，然后选择没有与新预订冲突的预订的房间.(旧预订在新预订开始之前结束，或者新的结束后就没问题了).

It works by joining the rooms to the reservations for that room, and then selecting the rooms for which there are no reservations that conflicts with the new reservation being made.(Old reservation that ends before the new one starts, or that starts after the new one ends are no problem).

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