duplicate when do the left join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

duplicate when do the left join

Author

Topic

kt
Yak Posting Veteran

88 Posts

Posted - 2014-01-27 : 17:45:01

select a.[Cuno], [doc_type], [Doc_name]
from [assembly] a
left join [assembly_Doc] doc
ON a.id = doc.assembly_id
left join [assembly_Recog] re
ON a.id = re.assembly_id
where [activate] =1
and doc.doc_type = 1

I only have two records for doc_type =1 in the [assembly_Doc] table. Without joining the third table [assembly_Recog], the results is corrected, 2 records returned. I need to join with [assembly_Recog] table, but when it joined, I got the duplicate records. It listed twice. pls see the results below and help.
Thanks

CusNo DocType DocName
CUS-022 1 B1489-01 Rev 1
CUS-022 1 B1489-01 Rev 1
CUS-022 1 B149-02 Rev 2
CUS-022 1 B149-02 Rev 2

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-27 : 17:54:03

It helps if you provide sample data, but, based on what you said, there are two rows in the assembly_Recog table that match each of the IDs from the assembly table.

A simple solution could be to add a DISTINCT to the select. But, it would probably be better to understand the data in the tables and why it is causing duplicates and join to the correct row. By, that I mean, what if there are two rows because that tables tracks changes. You would, probably, want to join to the most recent version (by date) to get the correct data from that table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-28 : 03:13:03

quote:
Originally posted by kt

select a.[Cuno], [doc_type], [Doc_name]
from [assembly] a
left join [assembly_Doc] doc
ON a.id = doc.assembly_id
left join [assembly_Recog] re
ON a.id = re.assembly_id
where [activate] =1
and doc.doc_type = 1

I only have two records for doc_type =1 in the [assembly_Doc] table. Without joining the third table [assembly_Recog], the results is corrected, 2 records returned. I need to join with [assembly_Recog] table, but when it joined, I got the duplicate records. It listed twice. pls see the results below and help.
Thanks

CusNo DocType DocName
CUS-022 1 B1489-01 Rev 1
CUS-022 1 B1489-01 Rev 1
CUS-022 1 B149-02 Rev 2
CUS-022 1 B149-02 Rev 2

the current way query is written it will effectively behave as an INNER JOIN as you've field from left joined table used in WHERE clause


select a.[Cuno], [doc_type], [Doc_name]
from [assembly] a 
left join [assembly_Doc] doc
ON a.id = doc.assembly_id
and doc.doc_type = 1 
left join [assembly_Recog] re
ON a.id = re.assembly_id
where [activate] =1

see
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources