I've got a reports table with organisation id's that has multiple rows for every year, example.
| ID | Organisation ID | Report Year
--------------------------------------
| 1 | 1 | 2016
| 2 | 1 | 2017
| 3 | 12 | 2016
| 4 | 12 | 2017
| 5 | 13 | 2016
| 6 | 13 | 2017
| 7 | 14 | 2016
and a report_files table as follows
| ID | Report Type ID | Report ID | Report File
---------------------------------------------------
| 1 | 1 | 1 | org1_test_report_2016.pdf
| 3 | 1 | 3 | org1_test_report_2016.pdf
| 5 | 1 | 5 | org1_test_report_2016.pdf
| 6 | 1 | 6 | org1_test_report_2017.pdf
| 7 | 1 | 7 | org1_test_report_2016.pdf
I'd like to get the most recent record from reports table i.e if 2016 and 2017 rows exist i'd like to get 2017 and if only 2016 exist then get that and left join that on reports_file table to get the report_file column with the file name if one exist for that id if not return null. Something like this.
| Report ID | Organisation ID | Report File
----------------------------------------------
| 2 | 1 | NULL
| 4 | 12 | NULL
| 6 | 13 | org1_test_report_2017.pdf
| 7 | 14 | org1_test_report_2016.pdf
Might not have explained properly, please let me know if the question is not clear. Basically want to group by organisation_id on the reports table but get the most recent row, if 2017 exists then get that if not get 2016 and then left join that on report_files table to see if a file is attached to the most recent report id if so return file else return null. Hope this makes sense.
Thanks in advance.
Try this:
SELECT C.Report_ID, A.Organisation_ID, C.Report_File
FROM
(SELECT Organisation_ID, MAX(Report_Year) Latest_Report_Year
FROM reports GROUP BY Organisation_ID) A
JOIN reports B ON A.Organisation_ID=B.Organisation_ID AND A.Latest_Report_Year=B.Report_Year
JOIN report_files C ON B.ID=C.Report_ID;
See it run on SQL Fiddle.