3

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
visionset
  • 134
  • 7
  • This is a great trick (it's even called a Strawberry join in fashionable circles!). But we don't use it any more because it doesn't scale nearly as well as more modern alternatives. – Strawberry May 25 '18 at 09:46

1 Answers1

3

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.

Nick
  • 138,499
  • 22
  • 57
  • 95
  • This somehow uses subqueries, which OP doesn't want. IMO, the question has no valid answer given OPs contraints (no subqueries nor aggreagates). But +1 for effort and valid output. – Michał Turczyn May 25 '18 at 10:01
  • @MichałTurczyn I think this is valid - I wouldn't really call that a subquery; it's just forcing the joins to proceed in a particular order, which I think is ok. – Strawberry May 25 '18 at 10:39
  • @MichałTurczyn I don't believe it's a subquery. The [MySQL spec](https://dev.mysql.com/doc/refman/5.7/en/join.html) says that it supports "nested joins" and the section on [nested join optimization](https://dev.mysql.com/doc/refman/5.7/en/nested-join-optimization.html) definitely seems to indicate that the joins are processed in order, not as separate subqueries. – Nick May 25 '18 at 12:13
  • This is exactly the solution I was after. Makes perfect sense, I'm just unfamiliar with the nesting constructs. Thank you @Nick. And thank you for the subquery discussion, I too was unsure when I first saw it. – visionset May 26 '18 at 11:38
  • No worries. I love a challenge! :) – Nick May 26 '18 at 13:55