Trouble with subqueries

Question

I have got three tables from which I need information. I am trying to get the license plate numbers for certain cars that much follow some guidelines.

The guidelines are: Cars that dont have any bookings from customers from city x. Cars that do not have any bookings from the table BOOKING.

Would appreciate any help I could get.

SELECT DISTINCT f.licenseplateNo, Model FROM Car c
JOIN Booking b
ON  c.licenseplateNo = b.licenseplateNo
JOIN Customer c
ON c.customerID = b.customerID
WHERE c.Ort <> 'cityname'
ORDER BY licenseplateNo ASC

SELECT f.licenseplateNo, Model FROM Cars c
WHERE NOT EXISTS
(SELECT b.licenseplateNo FROM Booking b WHERE b.licenseplateNo = c.licenseplateNo);

The problem with this code is shows as two separate questions.

Answer 1

Cars that don't have any bookings from customers from city x

select *
from bookings b, customers c, cars ca
where b.licenseplateNo = ca.licenseplateNo
and c.CustomerID = b.CustomerID
where not c.city = x;

Cars that do not have any bookings from the table BOOKING.

select *
from cars c
where not exists (
  select b.licenseplateNo 
  from bookings b
  where b.licenseplateNo = c.licenseplateNo
);

You could also do the second query with a left outer join between cars and bookings and check for null.

Cars that do not have any bookings from the table BOOKING and don't have any bookings from customers from city x

The requirement to exclude car from specific booking customer city excludes a subset of the requirement exclude all cars that have a booking. Hence tge second query takes care of that unless ofcourse we want a UNION of the 2 query results.

You can perform a Union of the two queries to get the combined results.

Another better option is to modify your 1st query to use a left join between car and booking and add an or check for null in your where.

SELECT DISTINCT f.licenseplateNo, Model
FROM Car c
LEFT JOIN Booking b
ON c.licenseplateNo = b.licenseplateNo
LEFT JOIN Customer c
ON c.customerID = b.customerID
WHERE c.Ort <> 'cityname'
OR c.Ort is NULL
ORDER BY licenseplateNo ASC;