CODEWITHSUNDEEP
sql-serverjoinsql-server-2012
Niharika
Niharika

Reputation: 71

joining 3 tables in sql server 2012 gives me duplicate records

I have three tables joined with the left outer join and I need to check two columns in both tables but in the right table, I got the more than one record from the same column so I want only the value exists to record, not value '0' record.

I have 3 tables

This is table 1 this is my second table this is my third table

for that, I have made this query result is almost there but it returns duplicate rows. this is my SQL query

SELECT PWA.DT,
       P.MOULDCODE AS DieNo, 
       T.AssemblyWt AS AWT,
       T.WaxWt AS WWt,
       T.CoreWt AS CWT
FROM PRCWaxAss PWA
     LEFT OUTER JOIN PRC P ON PWA.PRCNO = P.PRCNO
     LEFT OUTER JOIN TDCWaxView T ON (P.MOULDCODE = T.DieNo)
                                 AND (P.METALCODE = T.MetalCode
                                   OR T.MetalCode = '0');

and this is my output Ourput

"TDC" table and "PRC" table joined with left outer I have compared "die no" and "metal code" in both table if both are matched in PRC table then it will okay but if not matched "metal code" then I need to show record "metal code" value as "0" in TDC. in output image red highlighted row I don't want. Can anyone help me..thank you in advance.

Upvotes: 0

Views: 72

Answers (1)

SS_DBA
SS_DBA

Reputation: 2423

The OR condition is giving you the multiple line output of what you don't want.

Give this a try and it should give you what you are looking for.

Replace this line (P.METALCODE = T.MetalCode OR T.MetalCode = '0')

With this T.METALCODE = CASE WHEN P.MetalCode = T.MetalCode THEN P.MetalCode ELSE '0' END

Upvotes: 1

Related Questions