This are the tables:
- Material (Code, ID)
- Itemtrans (IteID, FtrID, Tradecode)
- Fintrade (Ftrdate, ID)
- Material.ID=Itemtrans.IteID
- Itemtrans.FtrID=Fintrade.ID
I want to list the last record on all the codes (Material.Code), tradecodes (Itemtrans.Tradecode) and Ftrdate (Fintrade.Ftrdate) based on Fintrade.Ftrdate (the date when registered).
I made this, but i think i am losing something :(
SELECT
MAT.CODE COD_ARTICOL,
FTR.FTRDATE DATA_DOCUMENT,
FTR.TRADECODE DOCUMENT
FROM ITEMTRANS ITM
LEFT JOIN
( SELECT
ID, TRADECODE,
MAX(FTRDATE) AS maxFTRdate
FROM FINTRADE
GROUP BY ID
) AS x ON x.ID = ITM.ITEid
LEFT JOIN FINTRADE FTR ON FTR.id = x.id
AND FTR.FTRDATE = x.maxFTRdate
LEFT JOIN MATERIAL MAT ON MAT.id = ITM.ITEID
LEFT JOIN FINTRADE FTR ON FTR.ID = ITM.FTRID
I need some help :(
Itemtrans.FtrID=Fintrade.ID
but you're joining the result of your subquery on, what amounts to,Fintrade.ID = Itemtrans.ITEid
. Is that on purpose?