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.