2

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 :(

4
  • You said 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?
    – user98890
    Jun 28, 2016 at 11:36
  • Could you provide your tables' structures as SHOW CREATE TABLE My_tables\G? Some sample data would be nice also? If there are confidentiality issues, you could SHA2() those bits.
    – Vérace
    Jun 28, 2016 at 17:59
  • 1
    On this line (no. 4) FTR.TRADECODE DOCUMENT - but you have no Ftr.tradecode field indicated in your schema. Is this linked to tradecode in Itemtrans? Problems like this are the reason why full schemas should be provided.
    – Vérace
    Jun 28, 2016 at 19:50
  • Groupwise max.
    – Rick James
    Jul 9, 2016 at 0:20

1 Answer 1

0
SELECT m.code,
       i.TradeCode,
       f.Ftrdate 
       FROM ( SELECT MAX( Fintrade.Ftrdate ) as Ftrdate,
                     Fintrade.ID as ID
                     FROM Fintrade
            GROUP BY Fintrade.Ftrdate )  as f
INNER JOIN (SELECT itemtrans.iteID as iteID,
                   itemtrans.FtrID as FtrID,
                   MAX( itemtrans.TradeCode ) as TradeCode
            FROM itemtrans 
            GROUP BY itemtrans.FtrID ) as i on f.ID = i.FtrID
INNER JOIN ( SELECT MAX( material.code ) as code,
                    material.id as id
            FROM material
            GROUP BY material.id ) as m
                    on i.iteID = m.id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.