CODEWITHSUNDEEP
sql-servert-sql
techBeginner
techBeginner

Reputation: 3850

compare two tables against their columns

I have 2 tables Table1 & Table2 both having say 10 columns

I want to compare these two tables against each of their column values and select only those records for which there is more than three column matches..

ie.,

Table1.Col1 value matches with Table2.Col1 value
AND Table1.Col2 value matches Table2.Col2 value
AND Table1.Col3 value matches Table2.Col3 value

OR

Table1.Col2 value matches with Table2.Col2 value
AND Table1.Col4 value matches Table2.Col4 value
AND Table1.Col6 value matches Table2.Col6 value and so on...

How to write a simple and smart query for this?

Upvotes: 2

Views: 2298

Answers (1)

Filip De Vos
Filip De Vos

Reputation: 11908

Join with an or condition and then filter based on the number of fields that are matching (I only worked up to 4 fields, add as many as you need).

select *
from table1 t1
join table2 t2
  on t1.field1 = t2.field1 
  or t1.field2 = t2.field2
  or t1.field3 = t2.field3
  or t1.field4 = t2.field4
where 
   ( case when t1.field1 = t2.field1 then 1 else 0 end 
   + case when t1.field2 = t2.field2 then 1 else 0 end 
   + case when t1.field3 = t2.field3 then 1 else 0 end 
   + case when t1.field4 = t2.field4 then 1 else 0 end
   ) >= 3

If you're lazy like me you can generate the statement for all fields like this.

select 'select * ' union all
select '  from table1 t1' union all
select '  join table2 t2' union all
select '    on 1 = 1 ' union all
select '   and t1.' + t1.name + ' = t2.' + t2.name  from sys.columns t1 join sys.columns t2 on t1.name = t2.name where t1.object_id = object_id('table1') and t2.object_id = object_id('table2')
union all
select 'where ( 0' union all
select '   + case when t1.' + t1.name + ' = t2.' + t2.name + ' then 1 else 0 end ' from sys.columns t1 join sys.columns t2 on t1.name = t2.name where t1.object_id = object_id('table1') and t2.object_id = object_id('table2')
union all 
select '      ) >= 3'

(Run the query and copy paste the result.)

Upvotes: 2

Related Questions