Skip to Content
0
Former Member
Apr 27, 2010 at 01:57 PM

Query help

43 Views

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