SQL join left get MAX (date)

Question

I have the following tables:

• notice
  • id INT
  • cdate datatime
  • ...
    
    
• subject
  • ID
  • name
    
    
• notice_theme
  • id_notice
  • id_theme

I want to receive the latest notifications for each topic.

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 very good. Idea? Thanks.

Answer 1

There are so many ways to solve this, but I'm used to doing it this way. An additional subquery is needed to separately calculate the last cDate for each 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