SQL join left get MAX(date)

Question

i have these tables :

• notice
  • id INT
  • cdate DATETIME
  • ...
    
    
• theme
  • id
  • name
    
    
• notice_theme
  • id_notice
  • id_theme

I want to get the latest notices for each theme.

SELECT id_theme, n.id
FROM notice_theme
LEFT JOIN (
    SELECT id, cdate
    FROM notice
    ORDER BY cdate DESC
) AS n ON notice_theme.id_notice = n.id
GROUP BY id_theme

The result is not good. An idea ? Thanks.

Answer 1

There are so many ways to solve this but I'm used to do it this way. An extra subquery is needed to separately calculate the latest cDate for every ID.

SELECT  a.*, c.*
FROM    theme a
        INNER JOIN notice_theme b
            ON a.ID = b.id_theme
        INNER JOIN  notice c
            ON b.id_notice = c.ID
        INNER JOIN
        (
            SELECT  a.id_theme, MAX(b.DATE_CREATE) max_date
            FROM    notice_theme a
                    INNER JOIN notice b
                        ON a.ID_Notice = b.ID
            GROUP   BY a.id_theme
        ) d ON  b.id_theme = d.id_theme AND
                c.DATE_CREATE = d.max_date

• SQLFiddle Demo