Trying to do a NOT IN query

Question

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

change the `or` to `and`? (not in will be slower over time, not exists will likely be faster over time. — xQbert, Dec 12 '16 at 18:48
@HartCO there basically a paremeter for the query... i wanna input the dates — leon Hill, Dec 12 '16 at 18:51
It seems like your query should be fine, is it returning unexpected results, error, no results? Are their time components on any of the date fields? — Hart CO, Dec 12 '16 at 19:01
@HartCO its just not returning any result for me.. no vehicles are showing up, is there a way t post a database on here to show what your on about — leon Hill, Dec 12 '16 at 20:11
I see `booking.enter_start_date` and can't help but think that `enter_start_date` is the parameter, and just `start_date` is the field in the booking table. In which case, try: `booking.start_date Between [enter_start_date] And [enter_end_date]`, same goes for the `OR` condition. I only think this is backwards because of the `enter` keyword. — MoondogsMaDawg, Dec 12 '16 at 20:24
@ChristopherD. yes i had them confused and now i can see all the vehicles listed but the between dates dont seem to be working its just showing the ones on the date i listed any idea why — leon Hill, Dec 12 '16 at 20:37
[This](http://stackoverflow.com/questions/5615225/hotel-reservation-system-sql-identify-any-room-available-in-date-range) question helps, but you likely won't solve this in the same way. The issue is your parameters create a range of dates on each of which needs to be the same available vehicle, but you are only testing the edges of the reservation. I suggest looking up "sql reservation availability" tutorials to understand how this problem is best tackled in a database setting. — MoondogsMaDawg, Dec 13 '16 at 02:26
@ChristopherD. Thanks man i have a feeling it is to do with the way i have relationships set up im gonna keep trying to solve it — leon Hill, Dec 13 '16 at 03:23

score 1 · Answer 1 · answered Dec 12 '16 at 18:46

1

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.

answered Dec 12 '16 at 18:46

Mark Adelsberger

42,148
4
35
52

sorry if i made it confusing i am basically trying to get a list of vehicles that are available for renting. for example if i input the date 10th Novemeber to 18 November i want to just see what vehicles are not being rented during that period.. as for your solution its giving me an error when i input that instead of what is inside my WHERE clause – leon Hill Dec 12 '16 at 18:50
I don't think it's your desired result that's confusing; you need to report errors more clearly. "its giving me an error" doesn't tell me anything; what does the error say? – Mark Adelsberger Dec 12 '16 at 19:04
This is the way that I do similar date queries. Better than the between operator. – Brent Oliver Dec 12 '16 at 19:41
@BrentOliver i cant seem to get it to work for me without a bracket a something being out of place would you write how its suppose to look for me if you have the time – leon Hill Dec 12 '16 at 20:12
I would use something like this where the #start_date and #end_date are the contents of your input fields. SELECT * FROM Vehicles WHERE Vehicles.vehicle_id NOT IN ( SELECT booking.vehicle_id FROM Booking WHERE ( #start_date <= booking.End_Date AND #end_date >= booking.Start_Date ) ); – Brent Oliver Dec 12 '16 at 20:35
@BrentOliver i keep getting the error that a comma or bracket is out of place and cant find where, where should i add one if i use this formula – leon Hill Dec 12 '16 at 20:41
@leonHill - If you need someone to diagnose an error message for you, you need to give COMPLETE INFORMATION ABOUT THE ERROR. That means in this case you need to tell us exactly what query you tried, and the exact text of the error message. – Mark Adelsberger Dec 12 '16 at 20:47
@MarkAdelsberger i was able to solve the problem with the bracket being out of place.... Now for some reason its not doing the between query and only a query on the two dates i enter – leon Hill Dec 12 '16 at 20:48
@leonHill If you are in Access and in query builder rather than sql mode then you might need square brackets [ ] around the column names??? – Brent Oliver Dec 13 '16 at 19:43

Learning2Code · Answer 2 · 2016-12-13T18:00:20.673

0

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

edited Dec 13 '16 at 18:00

answered Dec 12 '16 at 20:02

Learning2Code

521
9
21

I edited to show the full query, but I was referring to the Select in your subquery. You are selecting from only the booking table, so you must be selecting columns from that table – Learning2Code Dec 12 '16 at 20:17
i had some of the names wrong on the table, but when i run it sometimes it gives me all the results back even when vehicles have been booked on that date. 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 ) ); – leon Hill Dec 12 '16 at 20:29
It seems ok. Try making sure your database date columns and your date inputs are the same type. – Learning2Code Dec 12 '16 at 20:35
I figured out my problem its not registering the between dates only the dates i entered.. any idea why ?? – leon Hill Dec 12 '16 at 20:38
On a second look, it seems that this logic would not handle the case of the entire existing booking falling in between the start and end date inputs. What if we added a third clause, to handle this case. I will edit answer to show this third case. – Learning2Code Dec 13 '16 at 17:57

Trying to do a NOT IN query

2 Answers2