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
WHERE project_id = 'FOR EACH PROJECT'
ORDER BY createdate DESC
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.
JOIN ( SELECT project_id
, MAX(createdate) AS createdate
BY project_id ) AS latest
ON latest.project_id = projects.project_id
ON updates.project_id = projects.project_id
AND updates.createdate = latest.createdate
That is exactly right, thanks! Worked perfectly! Forgot you could do AND in the join statement. Thanks again!