SQL Question of three tables (certainly one of which is a junction)

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!)

View post on imgur.com

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:


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
    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!

Leave a Reply

Your email address will not be published. Required fields are marked *