CODEWITHSUNDEEP
mysqlsql
user4495602
user4495602

Reputation:

MySQL - Find available dates

I'm trying to do a selection that that displays the total amount of cars available at a given time slot (2016-04-22 to 2016-04-23) for a specified destination.

In this case I want to see how many cars that are available for rental in Mandal. However, in my query I'm also given the total amount of available cars that isn't in Mandal even though I've specified the destination. 

SELECT destination, COUNT(destination) AS 'available cars'
FROM cars
LEFT JOIN orders
  ON cars.id = orders.car_id
WHERE orders.car_id IS NULL
  OR date_to NOT BETWEEN '2016-04-22' AND '2016-04-23'     
  AND date_from NOT BETWEEN '2016-04-22' AND '2016-04-23'
  AND destination = 'Mandal'
GROUP BY destination

Running the query above gives me the following table: https://i.sstatic.net/Ck0s0.png

Along with available cars in Mandal it also returns only one available car in Stavanger which isn't supposed to be displayed nor is it correct. In Stavanger there are supposed to be two cars available (one that is leased at a different time, NOT BETWEEN '2016-04-22' AND '2016-04-23', and one that has not been leased yet, orders.car_id IS NULL). The amount of available cars in Mandal is correct, though.

Upvotes: 0

Views: 79

Answers (2)

Rion Williams
Rion Williams

Reputation: 76547

Parentheses are very important when building complex WHERE clauses as they can be used to group "ranges" as you are currently using :

   SELECT destination, 
          COUNT(sted) AS 'available cars'
     FROM cars
LEFT JOIN orders ON cars.id = orders.car_id
    WHERE (orders.car_id IS NULL)
       OR (date_to NOT BETWEEN '2016-04-22' AND '2016-04-23')     
      AND (date_from NOT BETWEEN '2016-04-22' AND '2016-04-23')
      AND (destination = 'Mandal')
 GROUP BY destination

You also may want to consider adjusting your ranges to simply use a less than / greater-than option to more accurately target the range (as opposed to the BETWEEN statements) and it might be a bit easier to read :

WHERE (date_from <= '2016-04-23' AND date_to >= '2016-04-22')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I strongly suspect that the where conditions need to go in the on clause:

SELECT destination, COUNT(sted) AS availablecars
FROM cars c LEFT JOIN
     orders o
     ON c.id = o.car_id AND
        date_to BETWEEN '2016-04-22' AND '2016-04-23' AND 
        date_from BETWEEN '2016-04-22' AND '2016-04-23' AND
        destination = 'Mandal'
WHERE o.car_id IS NULL
GROUP BY destination;

Without sample data, I'm not 100% sure. Also, it is possible that the date conditions should be:

     ON c.id = o.car_id AND
        date_to >= '2016-04-22' 
        date_from <= '2016-04-23' AND
        destination = 'Mandal'

Upvotes: 1

Related Questions