Select complete row with the latest datetime field after some "left joins"

Question

Yeah it seems to be answered multiple times, but everything I tried failed.

The most similar stackoverflow's post is for sure : select rows in sql with latest date for each ID repeated multiple times But the major difference here is that I need to group by after performing some left joins before.

Here is the case :

I have 3 tables (transaction, support, and transaction_support that links the 2 previous tables)

create TABLE `transaction`
(
  id INT,
  date_time DATE,
  notes TEXT,
  PRIMARY KEY (id)
);

create TABLE `support`
(
  id int,
  support_number int ,
  PRIMARY KEY (id)
);

create TABLE `transaction_support`
(
  id INT,
  transaction_id int,
  support_id int,  
  PRIMARY KEY (id), 
  FOREIGN KEY (transaction_id) REFERENCES transaction(id),  
  FOREIGN KEY (support_id) REFERENCES support(id)
);

INSERT INTO `support` values (1, 1111);
INSERT INTO `support` values (2, 2222);
INSERT INTO `support` values (3, 3333);

INSERT INTO `transaction` values (1, '1996-06-28 00:00:00', 'Old data, we shouln''t see it');
INSERT INTO `transaction` values (2, '1996-07-16 00:00:00', 'Old data, we shouln''t see it');
INSERT INTO `transaction` values (3, '2001-04-10 00:00:00', 'Old data, we shouln''t see it');
INSERT INTO `transaction` values (4, '2001-05-14 00:00:00', 'Lastest data from Danny');
INSERT INTO `transaction` values (5, '2001-05-14 00:00:00', 'Lastest data from John');
INSERT INTO `transaction` values (6, '2001-04-10 00:00:00', 'Old data, we shouln''t see it');

INSERT INTO `transaction_support` values (487131, 1, 2);
INSERT INTO `transaction_support` values (488504, 2, 2);
INSERT INTO `transaction_support` values (751126, 3, 2);
INSERT INTO `transaction_support` values (758188, 4, 2);
INSERT INTO `transaction_support` values (4444, 5, 3);
INSERT INTO `transaction_support` values (4445, 6, 3);

Here is a request try :

SELECT s.id AS s_id, t.*, MAX(t.date_time) AS `this date is good`
FROM support AS s
LEFT JOIN transaction_support AS ts ON ts.support_id = s.id
LEFT JOIN transaction AS t ON ts.transaction_id = t.id
GROUP BY ts.support_id

Another try with a sub-query :

SELECT s.id as support_id, t.*, sub.*
FROM support AS s
LEFT JOIN transaction_support AS ts  ON ts.support_id = s.id 
LEFT JOIN transaction AS t ON ts.transaction_id = t.id 
LEFT JOIN (
    SELECT ts.support_id AS `sub_support_id`,
            t.id AS `sub_transaction_id`,
            MAX(t.date_time) AS `sub_last_date`
    FROM transaction_support AS ts 
    LEFT JOIN transaction AS t ON ts.transaction_id = t.id 
    GROUP BY ts.support_id
 ) sub ON ts.support_id = sub.sub_support_id AND t.date_time = sub.sub_last_date
GROUP BY s.id

Expected result would be :

|support_id | transaction_id | transaction_notes       | transaction_date|
|-----------|----------------|-------------------------|-----------------|
| 1         | null           | null                    | null            |
| 2         | 4              | Lastest data from Danny | 2001-05-14      |
| 3         | 5              | Lastest data from John  | 2001-05-14      |

I tried many requests, with and without sub-queries, but so far I never got all latest data from transaction table when I "group by" a support ID.

But I'm pretty sure I need a sub-query...

Here is a fiddle : http://sqlfiddle.com/#!9/adc611/20

Some other similar posts I tried :

• GROUP BY with MAX(DATE)
• SQL select only rows with max value on a column
• MySQL select MAX(datetime) not returning max value

If anyone can help me figuring out the solution... thank you ! :)

Answer 1

For your version of MySql there is no simple solution.
You can use NOT EXISTS to get the data for the latest date_time for each support_id and join support to that resultset:

SELECT s.id AS support_id, 
       x.id AS transaction_id,  
       x.notes AS transaction_notes,
       x.date_time AS transaction_date
FROM support AS s
LEFT JOIN (
  SELECT ts.support_id, t.id, t.notes, t.date_time
  FROM transaction_support ts INNER JOIN transaction t
  ON ts.transaction_id = t.id
  WHERE NOT EXISTS (
    SELECT 1
    FROM transaction_support ts2 INNER JOIN transaction t2
    ON ts2.transaction_id = t2.id
    WHERE ts2.support_id = ts.support_id AND t2.date_time > t.date_time
  )
) AS x ON x.support_id = s.id

See the demo.
Results:

> support_id | transaction_id | transaction_notes       | transaction_date
> ---------: | -------------: | :---------------------- | :---------------
>          1 |           null | null                    | null            
>          2 |              4 | Lastest data from Danny | 2001-05-14      
>          3 |              5 | Lastest data from John  | 2001-05-14

Answer 2

If you want the latest transaction per support, one option uses a subquery for filtering in the on clause of the left join:

select s.*, t.*
from support s
left join (
    select t.*, ts.support_id
    from transaction_support ts 
    inner join transaction t 
        on  t.id = ts.transaction_id
        and t.date_time = (
            select max(t1.date_time)
            from transaction_support ts1
            inner join transaction t1 on t1.id = ts1.transaction_id
            where ts1.support_id = ts.support_id
        )
) t on s.id = t.support_id