酒店预订系统SQL:确定日期范围内可用的任何房间 [英] hotel reservation system SQL: identify any room available in date range

问题描述

((如果这看起来很熟悉:我问了与这个问题类似的不同问题，但我刚刚注意到站点设计已更改，现在所有者 do 想要一种方法来搜索某个范围之间可用的任何房间日期.所以这是一个新问题...)

(In case this seems familiar: I asked a different question similar to this one, but I just got a note that the site design has changed and now the owners do want a way to search for any room that is available between a range of dates. So this is a new question...)

我正在使用酒店预订系统，我需要查找在某个日期范围内可用的房间.现有的可用性"表架构很简单，列为:

I'm working on a hotel reservation system and I need to find which rooms are available between a range of dates. The existing 'availability' table schema is simple, the columns are:

room_id
date_occupied - a single day that is occupied (like '2011-01-01')

因此，例如，如果1月1日至1月5日间#6房间被占用，则将五行添加到可用性表中，每一天房间被占用.

So, if, for example, room #6 is occupied from January 1 to January 5, five rows are added to the availability table, one for each day that room is occupied.

我试图找出查询以查找开始日期和结束日期之间可用的房间(同时还过滤其他一些参数，例如禁烟，床位等)...在伪SQL中有点像:

I'm trying to figure out the query to find what rooms are available between a start and end date (while also filtering on some other parameters like non-smoking, bed size, etc.)... in pseudo-SQL it would be sort of like:

SELECT (a list of room IDs) FROM rooms, availability
 WHERE rooms.nonsmoking = 1
   AND rooms.singlebed = 1
   AND nothing between start_date and end_date is in availability.date_occupied

与我的其他问题一样，我在尝试确定确切的查询以及如何以最有效的方式将其联接到表时有些卡住.感谢您的帮助.

As with my other question, I'm a bit stuck trying to determine the exact query and how to join the tables it in the most efficient way. Thanks for the help.

推荐答案

如果我正确理解了您的数据库结构，则需要在房间中查找一行，而没有可用的相应行.

If I understood your db structure properly, you need to find a row in rooms with no corresponding rows in availability.

SELECT r.* 
FROM rooms r
  LEFT JOIN availability a ON (r.id = a.room_id 
 AND a.date_occupied BETWEEN :start_date AND :end_date)
WHERE a.id IS NULL

这篇关于酒店预订系统SQL:确定日期范围内可用的任何房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！