I’m trying to construct a question that may seize values from 2 tables which are associated by a junction desk. I’ve included a screenshot of the tables in query. (apparently can not embed footage but, first put up!)
In my question I want all of the fields from employee and the NEWEST LMIA.lmia_number. The difficulty I’m operating into is every time I embrace lmia_number within the question, staff which are in a couple of LMIA relation, the employee information present up as duplicates and the LMIA fields are completely different. I’ve included one of many queries the place I really feel I used to be the closest to getting it Ex:
SELECT DISTINCT employee.worker_id, LMIA.lmia_id, LMIA.lmia_number, LMIA.date_created FROM LMIA INNER JOIN worker_lmia ON worker_lmia.lmia_id = LMIA.lmia_id INNER JOIN employee ON employee.worker_id = worker_lmia.worker_id INNER JOIN (SELECT lmia_id, MAX (date_created) as maxDate FROM LMIA GROUP BY lmia_id ) ij ON worker_lmia.lmia_id = ij.lmia_id AND LMIA.date_created = ij.maxDate
This returns: https://i.stack.imgur.com/JBT1s.png
The place my purpose is to retrieve 1 row per employee with most up-to-date lmia_number and date_created (utilizing lmia.date_created).
Any assist can be vastly appreciated!