I have two tables (1. orders and 2. cars):
I'm trying to find all cars that are available at a given date. In this case I want to find all available cars between 2016-05-03 and 2016-05-05. I check for cars that are
orders.car_id IS NULL
SELECT destination, COUNT(destination) AS 'available cars'
LEFT JOIN orders ON cars.id = orders.car_id
WHERE (orders.car_id IS NULL
date_to NOT BETWEEN '2016-05-03' AND '2016-05-05'
AND date_from NOT BETWEEN '2016-05-03' AND '2016-05-05'
AND destination = 'Kristiansand' GROUP BY destination
I'm trying to fetch all cars available for rental outside of the given
dates in Kristiansand.
Let's say you have 2 periods T1 and T2 to check to see if they overlap you do this check (T1.start <= T2.end) AND (T1.end >= T2.start).
so try this below query, (it checks and makes sure that there doesn't exist an order of the same car that overlap the specified period
SET @startdate = '2016-05-03',@enddate = '2016-05-05'; SELECT c.destination,COUNT(c.destination) as available_cars FROM cars c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.car_id = c.id AND o.date_from <= @enddate AND o.date_to >= @startdate) AND c.destination = 'Kristiansand' GROUP BY c.destination
You can remove the SET statement and hardcode in your @enddate and @startdate