Joshua Gertner Joshua Gertner - 1 year ago 78
MySQL Question

Make sure there is no overlapping

I am trying to make a SQL statement for a project I am working on. It should return all

that are the right size vehicle and are not already scheduled at that date in the rentals table.

Schema (of 2 tables involved)

Vehicles(vehicleId, size)
Rental(rentId, customerId, vehicleId, startdate, enddate, cost)

SELECT vehicles.vehicleId
FROM vehicles
WHERE vehicles.size= "van"
AND vehicles.vehicleId <>
(SELECT rental.vehicleId
FROM rental
WHERE (rental.startdate BETWEEN '2016-04-27' AND '2016-04-30')
OR (rental.enddate BETWEEN '2016-04-27' AND '2016-04-30' ))

The first part of the statement, before the AND, returns all the vehicleid with size van and the second part, after the AND, should remove any vehicleid that is scheduled in the time frame by checking if either the startdate or enddate overlap. But the second part is not working like that and when run the whole thing returns nothing.

What is wrong with the statement.

Answer Source

You didn't quote your dates, so they're not dates - they're math expressions:

 rental.enddate BETWEEN 2016-04-27 AND 2016-04-30

executes as if it was "2016 minus 4 minus 27":

 rental.enddate BETWEEN 1985 AND 1982

They should all be quoted:

 rental.enddate BETWEEN '2016-04-27' AND '2016-04-30'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download