Find DISTINCT LAST record with SQL LEFT JOIN-CodePudding

I have two related tables:

CREATE TABLE Cars (
    id INT NOT NULL  AUTO_INCREMENT,
    plate VARCHAR(16) NOT NULL,
    flag TINYINT,
    PRIMARY KEY(id)
)

and:

CREATE TABLE Rents (
    id INT NOT NULL AUTO_INCREMENT,
    out_date DATE NOT NULL,
    in_date DATE,
    car_id INT,
    FOREIGN KEY (car_id) REFERENCES Cars(id),
    PRIMARY KEY(id)
)

I can have multiple rents for each car (0 to many).

I need to select all vehicles in table Cars (with flag = 1) along with their status i.e. I need to know if each car is currently unavailable (only out_date is filled) or availabe (out_date and in_date filled) of course also vehicles without any rents are to be considered available.

I tought to use something like:

SELECT 
    *,
    IF(Rents.in_date IS NOT NULL AND Rents.out_date IS NOT NULL, 1, IF(Rents.id IS NULL, 1, 0)) AS status
FROM Cars 
LEFT JOIN Rents ON Cars.id = Rent.Car_id WHERE Cars.Flag = 1

but this of course will just return all the rows with positive flag match and a status evaluation (0 unavailable, 1 available):

id | plate    | flag | id | out_date  | in_date      | car_id | status
---------------------------------------------------------------------
'1', 'FA787MX', '1',  '1', '2022-07-14', '2022-07-15', '1',     '1'
'1', 'FA787MX', '1',  '2', '2022-07-16', NULL,         '1',     '0'
'3', 'AB124DF', '1',  '4', '2022-07-13', '2022-07-14', '3',     '1'
'4', 'CC666VC', '1',  NULL, NULL,        NULL,         NULL,    '1'
'5', 'GG435ED', '1',  '5', '2022-07-16', NULL,         '5',     '0'

I would like to have just the second row of FA787MX car (i.e. the most recent out_date value).

Data sample:

INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (1, 'FA787MX', 1); 
INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (2, 'EX431YY', 0); 
INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (3, 'AB124DF', 1); 
INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (4, 'CC666VC', 1); 
INSERT INTO `Cars` (`id`, `plate`, `flag`) VALUES (5, 'GG435ED', 1); 

INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (1, '2022-07-14', '2022-07-15', 1); 
INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (2, '2022-07-16', NULL, 1); 
INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (3, '2022-07-16', NULL, 2); 
INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (4, '2022-07-13', '2022-07-14', 3); 
INSERT INTO `Rents` (`id`, `out_date`, `in_date`, `car_id`) VALUES (5, '2022-07-16', NULL, 5);

CodePudding user response：

One option is to join to find only those rentals that are still outstanding (in_date IS NULL). That will drop the old rentals having in_date not null.

Try this (fiddle):

SELECT Cars.*
     , Rents.in_date
     , CASE WHEN in_date IS NOT NULL OR Rents.id IS NULL THEN 1 ELSE 0 END AS status_final
  FROM Cars 
  LEFT JOIN Rents
    ON Cars.id = Rents.Car_id
   AND in_date IS NULL
 WHERE Cars.Flag = 1
;

and if the results contain only those with in_date IS NULL, this reduces to:

SELECT Cars.*
     , out_date
     , Rents.in_date
     , Rents.id IS NULL AS status_final
  FROM Cars 
  LEFT JOIN Rents
    ON Cars.id = Rents.Car_id
   AND in_date IS NULL
 WHERE Cars.Flag = 1
;

Result:

id	plate	flag	out_date	status_final
1	FA787MX	1	2022-07-16	0
3	AB124DF	1		1
4	CC666VC	1		1
5	GG435ED	1	2022-07-16	0

CodePudding user response：

If you imagine the result of your query as a table, you can easily write a query that would give you what you need (the subquery is just yours with the select spelled out to give a unique column name to the second id column, as it seemed useful - the only way to uniquely identify a row):

SELECT MAX(rent_id) FROM (
    SELECT 
        Cars.id as id,
        plate,
        flag,
        Rents.id as rent_id,
        out_date,
        in_date,
        car_id,
        IF(Rents.in_date IS NOT NULL AND Rents.out_date IS NOT NULL, 1, IF(Rents.id IS NULL, 1, 0)) AS status
    FROM Cars 
    LEFT JOIN Rents ON Cars.id = Rents.car_id WHERE Cars.Flag = 1
) as rental_status
WHERE status = 0
GROUP BY car_id;

Which tells you which rows are interesting:

 -------------- 
| MAX(rent_id) |
 -------------- 
|            2 |
|            5 |
 --------------

Now you can use a join to return the results of your initial query only for the interesting rows. To avoid having to spell out that query all over again, MySQL 8 has a way to stash the results of your core query and use it like a table:

WITH
  status_data AS (
    SELECT 
        Cars.id as id,
        plate,
        flag,
        Rents.id as rent_id,
        out_date,
        in_date,
        car_id,
        IF(Rents.in_date IS NOT NULL AND Rents.out_date IS NOT NULL, 1, IF(Rents.id IS NULL, 1, 0)) AS status
    FROM Cars 
    LEFT JOIN Rents ON Cars.id = Rents.car_id WHERE Cars.Flag = 1
)

SELECT * from status_data
JOIN (
    SELECT MAX(rent_id) as rent_id FROM status_data
    WHERE status = 0
    GROUP BY car_id
) as ids using(rent_id);

Giving the result:

 --------- ---- --------- ------ ------------ --------- -------- -------- 
| rent_id | id | plate   | flag | out_date   | in_date | car_id | status |
 --------- ---- --------- ------ ------------ --------- -------- -------- 
|       2 |  1 | FA787MX |    1 | 2022-07-16 | NULL    |      1 |      0 |
|       5 |  5 | GG435ED |    1 | 2022-07-16 | NULL    |      5 |      0 |
 --------- ---- --------- ------ ------------ --------- -------- --------