Between query within a database

Question

I've been trying to solve this problem for ages and still have no luck.

I am trying to set up a vehicle database in where I check the availability of vehicles on dates in which I enter for people can rent them out. For example, they want to rent a vehicle from November 05/11/2016 till 20/11/2016.

I have been able to connect the query back to the vehicle table but when i test the query it either gives me all the vehicles being listed as available or none of them.

I have included the tables and the relationships as I think the table between each one may be causing me the problem.

Any suggestion to fix this code is much appreciated.

Relationships Tables

SELECT *
FROM Vehicles
WHERE Vehicles.vehicle_id NOT IN 
        (
        SELECT distinct 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
    )
 );

Answer 1

I think of this as a left join:

select v.*
from vehicles v left join
     bookings b
     on v.vehicle_id = b.vehicle_id and
        b.start_rent_date <= @EnterEndDate and
        b.end_rent_date >= @EnterStartDate
where b.vehicle_id is null;

A vehicle is not free if there is a rental that starts before the desired end date and that ends after the desired start date. The left join excludes these. You could also use NOT EXISTS or NOT IN for equivalent logic.

EDIT:

Oh, you are using MS Access . . .

select v.*
from vehicles as v left join
     (select b.*
      from bookings as b
      where b.start_rent_date <= @EnterEndDate and
            b.end_rent_date >= @EnterStartDate
     ) as b
     on v.vehicle_id = b.vehicle_id
where b.vehicle_id is null;

Answer 2

SELECT  *
FROM    Vehicles
WHERE   Vehicles.vehicle_id NOT IN (
                                    SELECT distinct D1.[vehicle id]
                                    FROM Booking D1
                                    WHERE ( DATEDIFF(DAY, D1.start_rent_date, @Enter_Start_Date)>=0
                                            And DATEDIFF(DAY, D1.end_rent_date, @Enter_Start_Date)<=0
                                            )
                                            OR 
                                            (
                                            DATEDIFF(DAY, D1.start_rent_date, @Enter_End_Date)>=0
                                            And DATEDIFF(DAY, D1.end_rent_date, @Enter_End_Date)<=0
                                            )
                                            OR 
                                            (
                                            DATEDIFF(DAY, D1.start_rent_date, @Enter_Start_Date)<=0
                                            And DATEDIFF(DAY, D1.end_rent_date, @Enter_End_Date)>=0
                                            )
                                    );