SQLite SELECT with JOIN needs to return row only if every row in second table is true for WHERE clau-CodePudding

I need the select to be empty even if only one row in the second table doesn't meet the dates condition. But each parking space has multiple reservations in Reservations table and even if one of the reservation meets the criteria of not being in the range I will get the ID of parking space.

My SELECT

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")

Table example: I don't want to SELECT ID_Parking_Space from this table, because first row doesn't meet my condition. But because second one does I do get the 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         |

In the full table there are more reservations and if one row doesn't meet the condition I need the select to fail.

CodePudding user response：

I think what you need is 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'
  )
);