订房查询 [英] Room Booking Query

问题描述

我在编写sql以从表中获取可用空间时遇到问题.

I have a problem in writing the sql to get the availables rooms from the tables.

我的表格结构如下.

Table Booking
ID | START_DATE | END_DATE


Table BookingRoom (Intermediate Table)
ID   | BOOKING_ID   |ROOM_ID

一个房间可以链接到许多预订，而一个预订可以包含很多房间

A Room can be linked to many bookings and a booking can contain many rooms

Table Room
Contains the ID room

我已经尝试过了，但是如果一个房间在不同的日期链接到2个不同的预订会比较麻烦，那么将仅使用第一个预订ID进行比较

I have Tried This but there is a probleme if a room is linked to 2 differents bookings in separate dates the comparaison will be maked with only the first booking id

SELECT DISTINCT r.ID FROM room AS r ,booking AS b,bookingroom AS br
 WHERE r.ID = br.ID_ROOM
 AND b.ID = br.ID_BOOKING
 AND (
           b.END_DATE < '05/14/2013'
        OR b.START_DATE > '05/15/2013'
     )

有人可以帮我编写SQL来获取在签到日期之间的可用房间.

推荐答案

如果您想要的只是在整个所需日期范围内可用的房间列表，那么类似以下的方法可能会起作用:

If all you want is the list of rooms available for the entire range of desired dates, then something like the following might work:

Select Room.Id
From Room
Where Room.Id Not In    (
                        Select RoomId
                        From BookingRoom
                            Join Booking
                                On Booking.Id = BookingRoom.BookingId
                        Where Booking.StartDate <= 'DesiredEndDate'
                            And Booking.EndDate >= 'DesiredStartDate'
                        )
Order By Room.Id

因此，使用原始示例，我们可能会得到:

So, using the original example, we might get:

Select Room.Id
From Room
Where Room.Id Not In    (
                        Select RoomId
                        From BookingRoom
                            Join Booking
                                On Booking.Id = BookingRoom.BookingId
                        Where Booking.StartDate <= '2013-05-15'
                            And Booking.EndDate >= '2013-05-14'
                        )
Order By Room.Id

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