Wednesday, February 10, 2010

Left Join with Distinct Statement in T-SQL

Problem:

SQL Left Join, it return multiple rows of the same items. I tried DISTINCT but that didnt work because its on Left Join. So my question how to add DISTINCT to Left Join?

Solution:
SELECT *
FROM dbo.LG_106_10_STLINE (NOLOCK) INNER JOIN
dbo.LG_106_ITEMS (NOLOCK) ON dbo.LG_106_10_STLINE.STOCKREF = dbo.LG_106_ITEMS.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_10_STFICHE (NOLOCK) ON dbo.LG_106_10_STLINE.STFICHEREF = dbo.LG_106_10_STFICHE.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_CLCARD (NOLOCK) ON dbo.LG_106_10_STFICHE.CLIENTREF = dbo.LG_106_CLCARD.LOGICALREF LEFT OUTER JOIN
dbo.L_CAPIDIV (NOLOCK) ON dbo.LG_106_10_STFICHE.BRANCH = dbo.L_CAPIDIV.NR LEFT OUTER JOIN
dbo.LG_106_SPECODES (nolock) LG_106_SPECODES_1
--This is I Changed
(select DISTINCT dbo.LG_106_SPECODES.SPECODE,dbo.LG_106_SPECODES.DEFINITION_ FROM dbo.LG_106_SPECODES (NOLOCK)) LG_106_SPECODES_1 ON dbo.LG_106_ITEMS.STGRPCODE = LG_106_SPECODES_1.SPECODE LEFT OUTER JOIN
dbo.L_TRADGRP (NOLOCK) ON dbo.LG_106_10_STFICHE.TRADINGGRP = dbo.L_TRADGRP.GCODE LEFT OUTER JOIN
dbo.LG_106_SPECODES LG_106_SPECODES_2 (NOLOCK) ON dbo.LG_106_10_STLINE.DELVRYCODE = LG_106_SPECODES_2.SPECODE LEFT OUTER JOIN
LG_106_PROJECT (NOLOCK) ON LG_106_10_STLINE.PROJECTREF = LG_106_PROJECT.LOGICALREF LEFT OUTER JOIN
dbo.LG_106_10_INVOICE (NOLOCK) ON dbo.LG_106_10_STFICHE.INVOICEREF = dbo.LG_106_10_INVOICE.LOGICALREF
WHERE (dbo.LG_106_10_STFICHE.TRCODE IN (12)) AND
(LG_106_SPECODES_2.SPECODETYPE IS NULL OR LG_106_SPECODES_2.SPECODETYPE = 0)
AND (dbo.L_CAPIDIV.FIRMNR = 106)

No comments: