MySQL查询问题: Reservation Like System

Question

有可能已预订或未预订的房间。预订时间为从(日期)到(日期)为止。人员按日期搜索房间:from_field 和til_field。正在尝试查找是否有可用房间。

SELECT rooms.* FROM rooms
LEFT JOIN reservations ON reservations.room_id = rooms.id
WHERE ( reservations.from > till_field OR reservations.till < from_field )

问题:如果单个预订成功，则查询似乎有可用的房间，即使另一个预订占用了该位置。

Answer 1

如何在没有预订的情况下检索房间:

如果没有预约，就没有reservations查询返回的行 => 您还必须检查该行( reservations.room_id IS NULL ):

SELECT rooms.* FROM rooms
LEFT JOIN reservations ON reservations.room_id = rooms.id
WHERE reservations.room_id IS NULL -- if there is no reservations
      OR reservations.from > till_field
      OR reservations.till < from_field

但要真正得到你想要的，你必须检查没有任何预订的房间:

<子>

• fromDate or tillDate between from_field and till_field




OR

• fromDate < from_field and tillDate > till_field

SELECT rooms.*
  FROM rooms
 WHERE NOT EXISTS (SELECT NULL
                   FROM reservations 
                  WHERE reservations.room_id = rooms.id
                    AND ((
                          reservations.from BETWEEN from_field AND till_field
                          OR
                          reservations.till BETWEEN from_field AND till_field
                         )
                        OR
                         (
                          reservations.from < from_field
                          AND reservations.till > till_field
                         )
                        )
                  )