Issue with Join in query?

24 Views

Hi All,

I have a query report for sales which is giving multiple data if the invoice is based on multiple sales order i.e 2 SO copied to 1 delivery and 1 delivery copied to single invoice.

SELECT DISTINCT concat(T13.BeginStr,right(T7.DOCNUM,6)) AS ' INVOICE No.',T7.DocDate as 'Invoice Date',T7.DocDueDate,

T1.DocNum ,T1.DocDate'DocDate',T7.CardCode as 'Customer Code', T7.CARDNAME as 'Customer Name',T7.U_otherref

,T3.DocNum As ' DC Ref#',

T3.DocDate as 'DC Date',

T7.SHIPTOCODE as 'Ship To Code',T6.Linenum,T6.ITEMCODE,T6.DSCRIPTION,T6.U_SaleType,T6.U_SpclDeal, cast(T15.UserText as VarChar) as 'Item Remarks',

T6.QUANTITY,T6.WhsCode as 'Wh Name',T6.UnitMsr as 'UoM',T6.PriceBefDi as 'Basic Price',

(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=1 and INV4.docentry=T7.Docentry and INV4.linenum = T6.Linenum )VAT,

(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=7 and INV4.docentry=T7.Docentry and INV4.linenum = T6.Linenum )Surcharge,

(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=18 and INV4.docentry=T7.Docentry and INV4.linenum = T6.Linenum )AddVat,

(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=8 and INV4.docentry=T7.Docentry and INV4.linenum = T6.Linenum )EntryTax,

(Select distinct isnull(Sum(INV4.taxsum),0) from INV4 where INV4.statype=4 and INV4.docentry=T7.Docentry and INV4.linenum = T6.Linenum )CST,

T7.NUMATCARD as 'Customer PO No.'

FROM

dbo.RDR1 T0 left JOIN

dbo.ORDR T1 ON T1.DocEntry = T0.DocEntry LEFT JOIN

dbo.DLN1 T2 ON T2.BaseEntry = T1.DocEntry LEFT JOIN

dbo.ODLN T3 ON T2.DocEntry = T3.DocEntry left JOIN

dbo.INV1 T6 ON T6.BaseEntry = T3.DocEntry LEFT outer JOIN

dbo.OINV T7 ON T7.DocEntry = T6.DocEntry LEFT JOIN

dbo.OITM T15 ON T15.ItemCode = T6.ItemCode LEFT JOIN

dbo.OSLP T8 on T1.SLPCode = T8.SlpCode LEFT JOIN

dbo.OHEM T9 on T1.OwnerCode = T9.empID LEFT JOIN

dbo.INV12 T10 on T7.Docentry = T10.DocEntry LEFT JOIN

dbo.OCRD T12 on T7.CardCode = T12.CardCode LEFT JOIN

NNM1 T13 ON T7.Series = T13.Series left join

OITB T14 on T14.ItmsGrpCod = T15.ItmsGrpCod

WHERE T6.BaseType = '15' and T7.Doctype = 'I' --and T7.docnum = '46000138'

Can someone please let me know which join is wrong here that its fetching multiple lines?

Thanks,

Joseph