Table joins - selecting max row from joined table

Databases
1

Hi all,

I’m sure this was asked before and I search but I’m just not getting it.

The main query that I want to run is like this (in the real query there are about 30 fields and 10 tables but i think if we can get it working simple it will be easy to roll out):

SELECT a.project_id, a.field1, a.field2, a.field3, f.createdate, f.tstatus, f.createby
FROM projects a
LEFT JOIN 'updates' f ON a.project_id = f.project_id
GROUP BY a.project_id

For the f. fields, I want all of them from the row that has the most recent date. I would get that from that table by doing this:

SELECT createdate, status, createby, project_id 
FROM 'updates' 
WHERE project_id = 'FOR EACH PROJECT' 
ORDER BY createdate DESC 
LIMIT 1

The project id will be in both tables. The one project in projects and have many updates linking to it and I just want the data from the most recent update. Can I use a subquery somehow? I just can’t figure it out. Thanks for the help.

2 
SELECT projects.project_id
     , projects.field1
     , projects.field2
     , projects.field3
     , updates.createdate
     , updates.tstatus
     , updates.createby
  FROM projects 
LEFT OUTER
  JOIN ( SELECT project_id
              , MAX(createdate) AS createdate
           FROM updates
         GROUP
             BY project_id ) AS latest
    ON latest.project_id = projects.project_id
LEFT OUTER
  JOIN updates
    ON updates.project_id = projects.project_id
   AND updates.createdate = latest.createdate

:slight_smile:

3

That is exactly right, thanks! Worked perfectly! Forgot you could do AND in the join statement. Thanks again!