How to select only one record of the left table based on its max date in MySQL?

Question

Below is my database structure

User
id       name
1        John
2        Doe
3        Smitt


Post
id       user_id      text
1        1            Hello
2        1            World
3        2            How are you?
4        3            Whatsup!
5        3            High five!


Comment
id       post_id      text                        created_at
1        1            Comment on Hello            2019-12-01
2        1            Another comment on Hello    2019-12-02
3        2            Comment on World            2019-12-03
4        1            Latest comment on Hello     2019-12-04
5        1            Some comment                2019-12-05
6        5            Five highs!                 2019-12-06
7        4            Same old, same old!         2019-12-07

How to get a list of user with only one Comment record based on its MAX(created_at) in MySQL as following?

Result
id        name       comment_text          created_at
1         John       Some comment          2019-12-05
2         Doe        NULL                  NULL
3         Smitt      Same old, same old!   2019-12-07

Condition: due to another use case, the flow must go from User table to Comment table, not vice versa!

Answer 1

You can left join, using a correlated subquery that retrieves the id of the latest post of the current user as a join condition:

select
    u.id,
    u.name,
    c.text comment_text,
    c.created_at
from user u
left join comment c 
    on c.id = (
        select c1.id
        from post p1
        inner join comment c1 on c1.post_id = p1.id
        where p1.user_id = u.id
        order by c1.created_at desc
        limit 1
    )

Demo on DB Fiddle:

  id | name  | comment_text        | created_at
---: | :---- | :------------------ | :---------
   1 | John  | Some comment        | 2019-12-05
   2 | Doe   | null                | null      
   3 | Smitt | Same old, same old! | 2019-12-07

Or, if you are running MySQL 8.0, you can use row_number():

select id, name, comment_text, created_at
from (
    select
        u.id,
        u.name,
        c.text comment_text,
        c.created_at,
        row_number() over(partition by u.id order by c.created_at desc) rn
    from user u
    left join post p on p.user_id = u.id
    left join comment c on c.post_id = p.id
) t
where rn = 1

Demo on DB Fiddle