CODEWITHSUNDEEP
mysqldatecompare
James Privett
James Privett

Reputation: 1109

MYSQL date compare query

I'm trying to return a list of vehicles where I know they are not already booked for a current date range. I have a tblVehicles table:

ID     Name
1      BMW
2      Merc
3      Land Rover
4      Hummer
5      Convertable

I also have a table (tblDiary) that holds all the dates that vehicle is being used:

ID     VehicleId     startDate     endDate
1      1             2016-06-20    2016-06-22
2      1             2016-06-24    2016-06-24
3      2             2016-05-01    2016-06-05

So I'm trying to write a query where I want to find vehicles that are available between 2016-06-21 & 2016-06-23. So, in this instance the desired result would be:

ID     Name
2      Merc
3      Land Rover
4      Hummer
5      Convertable

What the most efficient way of doing this?

Upvotes: 2

Views: 51

Answers (1)

Quassnoi
Quassnoi

Reputation: 425803

SELECT  *
FROM    tblVehicles v
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    tblDiary d
        WHERE   d.vehicleId = v.id
                AND endDate >= '2016-06-21'
                AND startDate <= '2016-06-23'
        )

Upvotes: 1

Related Questions