CODEWITHSUNDEEP
sqlms-access
leon Hill
leon Hill

Reputation: 93

Trying to do a NOT IN query

I am new to SQL and I am trying to design a database in relation to a vehicle leasing company. At the moment I am trying to check if a vehicle is available for renting (i.e. has it been rented out already). I have two different tables one for bookings where the dates of the vehicle being rented is being stored and then one for where all my vehicles are being stored. I think I currently got them to connect but can't seem to get the dates to work for me so I must have something wrong. I will also include a picture of the tables so you can see where I am getting the field names from. Any help is much appreciated.

Table fields name

 SELECT *
 FROM Vehicles
 WHERE Vehicles.vehicle_id NOT IN (
 SELECT Booking.[vehicle id]
 FROM Booking
 WHERE (
    [Enter Start Date] BETWEEN booking.start_rent_date
        AND booking.end_rent_date
    )
 OR (
    [Enter End Date] BETWEEN booking.start_rent_date
        AND booking.end_rent_date
    )
  );

Upvotes: 0

Views: 81

Answers (2)

Learning2Code
Learning2Code

Reputation: 521

Shouldn't it be Booking.vehicle_id

SELECT *  FROM Vehicles 
WHERE Vehicles.vehicle_id NOT IN (
SELECT Booking.vehicle_id
FROM Booking
WHERE (
        start_date BETWEEN booking.enter_start_date
            AND booking.enter_end_date
        )
    OR (
        end_date BETWEEN booking.enter_start_date
            AND booking.enter_end_date
        )
    OR (
        booking.enter_start_date BETWEEN start_date
           AND end_date
    )
);

Upvotes: 0

Mark Adelsberger
Mark Adelsberger

Reputation: 45659

The main issue I see is that you're testing whether the start date or the end date occurs within the booking period, but you probably should be checking whether the period [start_date, end_date] overlaps the booking period at all. If that accounts for the error you're seeing, then try changing the date logic in the inner query to

    start_date <= booking.enter_end_date
and end_date >= booking.enter_start_date

If that's not the issue, then you probably need to be more specific about what you're getting and how it differs from what you expect.

Upvotes: 1

Related Questions