SQL，选择与您找到的实体不相等的实体

Question

SELECT HOTEL.ROOM
FROM HOTEL
  LEFT OUTER JOIN BOOKED
 ON BOOKED.ROOM = HOTEL.ROOM
 WHERE
    TO_DATE('&STARTDATE','DD-MM-YY') BETWEEN BOOKED.ARRIVALDATE 
 AND BOOKED.ARRIVALDATE + &NIGHTS

我需要

帮助，我需要搜索已预订的房间并返回可用的房间。基本上，用户在查询中输入日期，以及他入住的晚数。这是在 BOOKED 表中查找的，如果日期相等，则意味着房间已预订，它不应该从酒店中选择那个房间。房间桌子。编辑：将右外连接更改为左外连接，刚刚意识到它从错误的表中选择数据

Answer 1

您日期范围内的预订：

SELECT *
FROM   BOOKED
WHERE  TO_DATE('&STARTDATE','DD-MM-YY') BETWEEN ARRIVALDATE AND ARRIVALDATE + &NIGHTS

扩展以查找此列表中的HOTEL房间，即预订：

SELECT ROOM
FROM   HOTEL
WHERE  EXISTS (
         SELECT *
         FROM   BOOKED
         WHERE  TO_DATE('&STARTDATE','DD-MM-YY') BETWEEN ARRIVALDATE AND ARRIVALDATE + &NIGHTS
         AND    ROOM = HOTEL.ROOM
       )

以及那些不在此列表中，即未预订的：

SELECT ROOM
FROM   HOTEL
WHERE  NOT EXISTS (
         SELECT *
         FROM   BOOKED
         WHERE  TO_DATE('&STARTDATE','DD-MM-YY') BETWEEN ARRIVALDATE AND ARRIVALDATE + &NIGHTS
         AND    ROOM = HOTEL.ROOM
       )