I have four tables. The first describing a mix of items. The second is a linking table between the mix, and the items. The third is the item table, and the fourth holds lot information - lot number, and when that lot starts being used.
mix
mixID | mixName
----------------
1 | Foxtrot
2 | Romeo
mixLink
mixID | itemID
----------------
1 | 1
1 | 2
1 | 3
item
itemID| itemName
----------------
1 | square
2 | triangle
3 | hexagon
itemLots
itemID| lotNo | startDate
-------------------------
1 | 22/5/3| 22/07/16
2 | 03/5 | 25/07/16
2 | 04/19 | 12/08/16
3 | 15/0 | 05/08/16
Now, I need to be able to fetch the information from the database, which details all the items from a mix, as well as the most recently used lot number, something like this:
itemName | lotNo
----------------
square | 22/5/3
triangle | 04/19
hexagon | 15/0
I've tried a dozen different mixes of joins, group by's, maxes, subqueries, and havings; all to no avail. Any help would be much appreciated, I've been pulling my hair out for hours, and I feel like my fingernails are just scraping at the solution!