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!
This will give you the result you're after and will perform pretty well if you have your indexes done properly. I'm not sure how you're meaning to reference mix as it's not apparent in your sample output but I've included it in the WHERE clause so hopefully you can understand where you would use it.
SELECT i.itemName
, (SELECT il.lotNo FROM itemLots il
WHERE il.itemID=i.itemID
ORDER BY il.startDate desc
LIMIT 1) as lotNo
FROM item i
JOIN mixLink ml ON ml.itemID=i.itemID
JOIN mix m ON m.mixID=ml.mixID
WHERE m.mixName="Foxtrot";