SQLite SELECT with JOIN只有在WHERE子句中第二表中的每一行都为真时才需要返回一行

Question

我需要选择为空，即使在第二个表中只有一行不符合日期条件。但是每个车位在reservations table中都有多个预订，即使其中一个预订满足不在范围内的条件，我也会得到停车位的ID。

我选择

SELECT Parking_Spaces.ID_Parking_Space FROM Reservations 
INNER JOIN Parking_Spaces ON Reservations.ID_Parking_space = Parking_Spaces.ID_Parking_Space
AND (Reservations.Arrival_time NOT BETWEEN "2000-12-15" AND "2005-12-15")
AND (Reservations.Departure_time NOT BETWEEN "2000-12-15" AND "2005-12-15")

表示例:我不想从这个表中SELECT ID_Parking_Space，因为第一行不符合我的条件。但是因为第二个我得到了ID。

|ID_RESERVATION |  Arrival_time  |    Departure_time   |  ID_Parking_Space
|:------------- |:--------------:|: ------------------:|:----------------:|
|      6        |   2001-12-15   |    2002-12-15       |        4         |
|      16       |   2009-12-15   |    2010-12-15       |        4         |

在满表中有更多的保留，如果有一行不满足条件，我需要选择失败。

Answer 1

我认为你需要的是NOT EXISTS:

SELECT p.ID_Parking_Space 
FROM Parking_Spaces p 
WHERE NOT EXISTS (
SELECT 1
FROM Reservations r 
WHERE r.ID_Parking_space = p.ID_Parking_Space
AND (
r.Arrival_time BETWEEN '2000-12-15' AND '2005-12-15' 
OR 
r.Departure_time BETWEEN '2000-12-15' AND '2005-12-15'
)
);