MySQL查询检查特定房间的可用性取决于两个输入日期

Question

我不知道如何检查可用房间取决于用户的两个输入日期。

假设有一位用户尝试在 2018-05-05 至 2018-05-06 之间 checkin 。

用户提交表单后，系统应检查该特定日期是否有可用房间。

返回的结果应该只有两种类型的房间:

Room_Name |  Price
standard  |  300
premier   |  900

MySQL 表

Reservations
ID | User | Check_in   | Check_out
1  | john | 2018-05-03 | 2018-05-07
2  | jane | 2018-05-04 | 2018-05-07

Reservations_Details
ID | Reservation_ID | Rooms_ID
 5 |      1         |   2
 6 |      2         |   3

Rooms
ID | Rooms_Type_ID | Room_No | Room_Status_ID
 1 |     1        |  101     |      1
 2 |     1        |  102     |      2
 3 |     1        |  103     |      1
 4 |     2        |  201     |      3
 5 |     3        |  301     |      1
 6 |     3        |  302     |      1

Rooms_Type
ID | Room_Name | Price | Units
 1 | standard  |  300  |  3
 2 | deluxe    |  600  |  1
 3 | premier   |  900  |  2

Room_Status
ID | Status 
 1 | vacant
 2 | occupied
 3 | reserved

查询

我有这样的查询，但这仅适用于状态 = 空置的可用房间

SELECT rooms_type.room_name, rooms_type.price 
    FROM rooms_type
    JOIN rooms ON rooms.rooms_type_id = rooms_type.id 
    WHERE rooms.status_id = 1 
    GROUP BY rooms_type.room_name

如果可能的话，我想不出如何在一个查询中生成结果。

Answer 1

假设您有开始日期和结束日期。它会给你所有可用房间的 ID

尝试这个查询

SELECT Rooms.Room_No
FROM Rooms
JOIN Reservations_Details ON rooms.ID = Reservations_Details.Rooms_ID 
JOIN Reservations ON Reservations.ID = Reservations_Details.Reservation_ID 
WHERE (start_date < Check_in AND start_date < Check_out AND 
       end_date < Check_in AND end_date < Check_out) OR
      (start_date > Check_in AND start_date > Check_out AND 
       end_date > Check_in AND end_date > Check_out)