Union producing Duplicates - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Union producing Duplicates

Author

Topic

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-10-28 : 11:02:03

I would like to do a Union between two tables and display the 'Matched' or 'UnMatched' columns

With MatchedValues
as
(
select
name,
surname,
MatchType = 'Matched'
from table1
inner join table2 on table1.ID = table2.ID and table2.ID is not null
where
table1.name = table2.name

Union

select
name,
surname,
MatchType = 'UnMatched'
from table1
Left Outer Join table2 on table1.ID = table2.ID
where
table1.name != table2.name
)

select * from MatchedValues
--Group By

Both 'Matched' and 'UnMatched' values are being outputted by this query so i am having duplicates.How can i
filter it to diplay one value either 'Matched' or 'UnMatched'. I guess i need a group BY, No?

Marcus

I learn something new everyday.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-28 : 11:47:07

You don't really have duplicates (in fact, you can't since you're using UNION). The Matched column discriminates. You could have two rows for the same name/surname combo, one for 'Matched' and one for 'Unmatched'. Note that the second part of your CTE has 'where table1.name != table2.name' which will match all rows of table2 except the ones where the names are the same as table1. I don't think that is what you want.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-28 : 11:52:06

[code]
select
name,
surname,
case when table1.name = table2.name then 'Matched' else 'UnMatched' end as MatchType
from table1
Left Outer Join table2 on table1.ID = table2.ID
[/code]

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-10-28 : 16:53:16

Thank you both... No need for Union Scott. Case will do just fine ,SUPER ..

Marcus

I learn something new everyday.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources