0

I an trying to write a SELECT statement that excludes results where the car has already been booked on a given day.

My code selects info from table Car_Details and links to table Booking_Details to get the dates that a car is already booked on.

Car 'AA11 AAA' is booked for both of the days '2019-05-09' and '2019-05-08' and car 'BB22 BBB' is not booked on any day.

The tables are linked using the Key Car_Registration

SELECT Car_Details.Car_Registration
FROM Car_Details, Booking_Details
WHERE Car_Details.Car_Active='Y'
    AND NOT(Booking_Details.Car_Registration=Car_Details.Car_Registration)
        AND Booking_Details.Booking_Date='2019-05-09')
GROUP BY Car_Details.Car_Registration;

When run I expect the result to be only car 'BB22 BBB' but instead I get both cars 'AA11 AAA' and 'BB22 BBB' appearing.

How do I edit this code so only car 'BB22 BBB' appears?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Jamie
  • 11
  • The extra ) is not part of the code I am using – Jamie May 08 '19 at 13:59
  • 1
    https://www.dofactory.com/sql/join – Carlo 1585 May 08 '19 at 14:03
  • This whole query is gibberish. See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry May 08 '19 at 15:05

1 Answers1

-1

You could do this in a couple of ways, a LEFT OUTER JOIN would work and filter the results where a value in Booking_Details is null, but I think it may be faster to do this with a subquery, I had to research this, but according to this StackOverflow post (Join vs. sub-query) the LEFT OUTER JOIN can be faster than a subquery, but only when the number of rows in the table is small, if your booking table could grow to a size of over 100k records, the subquery is then better performance without aggregrate functions per Strawberry, modification, GROUP BY is not necessary when taking the subquery approach as it won't return duplicates:

SELECT car.Car_Registration
FROM Car_Details AS car
WHERE car.Car_Active = 'Y' AND
    car.Car_Registration NOT IN (
                                     SELECT Car_Registration
                                     FROM Booking_Details
                                     WHERE Booking_Date='2019-05-09'
                                   );
Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40