Retrieve max/min row with left join trick for a group, when group is on another table

Question

I am trying to apply the left join trick to the below tables. This trick is well documented here and involves a left self join on a comparison of the field you require the min or max of, then the left join produces a null for the min or max row, you then select that null match. However I am having a problem solving this when the group field is on another table. The tables below are joined by messjoin.fk_mess = message.id and I have included my best attempt at the query. It is currently failing to do the grouping.

Here is a fiddle example when the group is on the same table as the min/max field

CREATE TABLE messages(`id` int, `when` date);

CREATE TABLE messjoin(`grp` int, `fk_mess` int);

INSERT INTO messages
    (`id`, `when`)
VALUES
    (1,'2000-08-14'),
    (2,'2000-08-15'),
    (3,'2000-08-16'),
    (4,'2000-08-17'),
    (5,'2000-08-18'),
    (6,'2000-08-19');
    
INSERT INTO messjoin
    (`grp`, `fk_mess`)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 4),
    (2, 5),
    (2, 6);


select p1.*, m1.*, m2.*
      from messjoin p1 
inner join messages m1 on p1.fk_mess = m1.id
inner join messjoin p2 on p2.fk_mess = m1.id
left  join messages m2 on p2.grp = p1.grp and m1.when < m2.when
where m2.id is null;
+------+---------+------+------------+------+------+
| grp  | fk_mess | id   | when       | id   | when |
+------+---------+------+------------+------+------+
|    2 |       6 |    6 | 2000-08-19 | NULL | NULL |
+------+---------+------+------------+------+------+

What I want is to produce the max date for each group of .grp, like so:

+------+---------+------+------------+------+------+
| grp  | fk_mess | id   | when       | id   | when |
+------+---------+------+------------+------+------+
|    1 |       3 |    3 | 2000-08-16 | NULL | NULL |
|    2 |       6 |    6 | 2000-08-19 | NULL | NULL |
+------+---------+------+------------+------+------+

I do not want a aggregate function or subquery solution! And this is in mysql

Thank you!

Answer 1

What you need to do to make this work is to LEFT JOIN the JOIN of the two tables to each other:

SELECT p1.*, m1.*, m2.*
FROM (messparent p1 JOIN messages m1 ON p1.fk_mess = m1.id)
LEFT JOIN
(messparent p2 JOIN messages m2 ON p2.fk_mess = m2.id)
ON m2.when > m1.when AND p2.grp = p1.grp
WHERE m2.id IS NULL

Output:

grp fk_mess id  when        id  when
1   3       3   2000-08-16  (null)  (null)
2   6       6   2000-08-19  (null)  (null)

SQLFiddle.