CODEWITHSUNDEEP
mysql
Francisco José Segura Noya
Francisco José Segura Noya

Reputation: 13

How many cars are in the garage? Select in two tables (Mysql)

Situation:

I have a table with five cars.

table cars: id_car, name_car

In another table are the periods of day when a car is outside the garage.

absences table: id_absence, car_id, departure_date, return_date

Issue: Select the cars that are in the garage on a certain day, for example the current day.

After many days I have come to:

SELECT  c.*, a.*
FROM cars c
LEFT JOIN  absences a
ON  c.id_car = a.car_id 
AND ('2019-04-02' NOT BETWEEN a.departure_date AND a.return_date)

It does not work because, although a car is absent, it appears selected, with the rest of the fields to NULL.

With INNER JOIN it does not work either.

Upvotes: 0

Views: 38

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31812

A little fix to your query to make it an "antijoin":

SELECT  c.*
FROM cars c
LEFT JOIN  absences a
  ON  c.id_car = a.car_id 
  AND '2019-04-02' BETWEEN a.departure_date AND a.return_date
WHERE a.car_id IS NULL

The same result can be achieved with a NOT EXISTS subquery:

SELECT  c.*
FROM cars c
WHERE NOT EXISTS (
    SELECT *
    FROM absences a
    WHERE c.id_car = a.car_id
      AND '2019-04-02' BETWEEN a.departure_date AND a.return_date
)

Upvotes: 0

Related Questions