db fiddle: https://www.db-fiddle.com/f/qKDzqVNxVHsMcnPd2JpuWM/8
I have a table of technicians:
Tech which is simply customer_id, tech, classification, allocation
I want to display customer_id, tech, name, classification, allocation
Problem is that name comes from ReportData (Reports have an entry for the current day PLUS every month end going back as long as they have been sending data )
I want to get the most recent name in the ReportData table to display alongside the tech (Worst case, just get names from the most recent active report and show null for ids not in current report)
- tech is non-unique and can be used for other customers (so need to filter tech by customer_id)
- customer_id is not on the ReportData table but is in the header table ReportHeader (So requires a multi JOIN)
I tried to use JOINs to get this data but it is not working as I expected.
See 7599 - It is showing the employee from 2017 - I'm guessing there has to be a better way to do this?