compare set of rows - 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 2005 Forums

Transact-SQL (2005)

compare set of rows

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2010-05-24 : 14:03:14

How can I compare all the set of rows to set of rows .. I am trying to compare #temp1 to #temp2 based on the combination of ROWS ( based on Id) ?

How can I do this T-SQL or SSIS

Select *
into #temp1
from (select 1 as ID , 2 as Seq , 1 as value
union all
select 1 , 2 , 1
union all
select 1 , 3 , 2
union all
select 1 , 6 , 8
union all
select 2 , 1 , 7
union all
select 2 , 2 , 10
union all
select 3 , 1 , 7
union all
select 3 , 3 , 10)a

select *
into #temp2
from
(
select 5 as ID , 2 as seq , 1 as value
union all
select 5 , 3 , 2
union all
select 5 , 6 , 8
union all
select 10 , 1 , 7
union all
select 10 , 2 , 10
union all
select 11 , 2 , 1
union all
select 11 , 1 , 7
union all
select 12 , 1 , 7
union all
select 12 , 3 , 10
)b

The output should look like

ID ID
5 1
10 2
12 3

jung1975
Aged Yak Warrior

503 Posts

Posted - 2010-05-24 : 14:50:00

Fro example ID 1

2 1
3 2
6 8

is matching with ID 5 in temp2
even though 2 1 exist in ID = 11 in Temp2.. it shouldn;t consider as a matching ID becasue ID 11 has 2 1 and 1 7

not
2 1
3 2
6 8

only ID = 5 in Temp2 has all the matching rows..

does it make sense ? thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources