CODEWITHSUNDEEP
sql-servert-sql
GrangerG
GrangerG

Reputation: 11

Find matched column records in one table that may be in multiple columns in a second table

I have two tables, Table 1 with multiple columns, name, ID number, address, etc. And Table 2 with columns, ID number 1 and ID number 2 and a few other columns.

I am trying to get a T-SQL query returning all rows in Table 1 with an indicator showing whether the ID number in Table 1 matches either ID_1 or ID_2 in Table 2. The result set would be all columns from Table 1 , plus the indicator “Matched” if the ID number in Table 1 matches either ID_1 or ID_2 in Table 2.

Result

T1.ID, Name, Address, ("Matched"/"Unmatched") ...

Also, would it be the same to do the opposite, meaning instead of the result including all rows from Table 1 that have a matching ID in ID_1 or ID_2 in Table 2, the result set would include only records from Table 1 where t1.ID = (T2.ID_1 or T2.ID_2)?

 SELECT DISTINCT  
     CASE 
        WHEN (table1.ID = table2.ID_1 ) 
           THEN 'Matched'  
           ELSE 'Unmatched'  
     END AS Status ,
     table1.*
 FROM
     table1  
 LEFT JOIN 
     table2 ON table1.ID = table2.ID_1 

 UNION

 SELECT DISTINCT  
     CASE 
        WHEN (table1.ID = table2.ID_2)  
           THEN 'Matched'  
           ELSE 'Unmatched'  
     END AS Status,  
     table1.*
 FROM     
     table1  
 LEFT JOIN 
     table2 ON table1.ID = table2.ID_2

Upvotes: 1

Views: 1024

Answers (1)

GMB
GMB

Reputation: 222432

I think that a correlated subquery with an exists condition would be a reasonable solution:

select
    t1.*,
    case when exists (select 1 from table2 t2 where t1.id in (t2.id_1, t2.id_2))
        then 'Matched'
        else 'Unmatched'
    end matched
from table1 t1

And the other way around:

select
    t2.*,
    case when exists (select 1 from table1 t1 where t1.id in (t2.id_1, t2.id_2))
        then 'Matched'
        else 'Unmatched'
    end matched
from table2 t2

If you want to "align" the rows based on the match for the whole dataset at once, then you might want to try a full join:

select t1.*, t2.*
from table1 t1
full join table2 t2 on t1.id in (t2.id_1, t2.id_2)

Upvotes: 1

Related Questions