Hi Folks,
i am working on this particular qurey and the purpose is to get all the items on the invoices along with their amount, SO# and also a credit Memo if it existed. The query works fine until I join the ORIN and RIN1 where it will show a combination of items and repeat certain items.Also, I just want the parent item but it show the child items and also applies cerdit memo to that. Can someone help me figure out where the join is not correct?
SELECT T3.DOCNUM SALESORDER, T3.DOCDATE ORDERDATE, T4.ITEMCODE,T4.LINETOTAL,T5.DOCNUM INVOICE, T5.DOCDATE DATE,T6.CARDNAME CUSTOMER,T10.DOCNUM CREDIT , T.LINETOTAL,T7.SLPNAME SALES, T8.COMMISSION COMMISSION
FROM DBO.DLN1 T0
INNER JOIN DBO.ODLN T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DBO.RDR1 T2 ON T0.BASEENTRY = T2.DOCENTRY AND T0.BaseLine = T2.LineNum
LEFT JOIN DBO.ORDR T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN DBO.INV1 T4 ON T4.BASEENTRY = T0.DOCENTRY AND T4.BaseLine = T0.LineNum
LEFT JOIN DBO.OINV T5 ON T4.DOCENTRY = T5.DOCENTRY
LEFT JOIN DBO.OCRD T6 ON T5.CARDNAME=T6.CARDNAME
LEFT JOIN DBO.OSLP T7 ON T6.SLPCODE=T7.SLPCODE
LEFT JOIN DBO.OCOG T8 ON T7.GROUPCODE=T8.GROUPCODE
LEFT JOIN DBO.OITM T9 ON T4.ITEMCODE=T9.ITEMCODE
LEFT JOIN DBO.ORIN T10 ON T10.NUMATCARD=T5.NUMATCARD
LEFT JOIN DBO.RIN1 T ON T.DOCENTRY=T10.DOCENTRY