CODEWITHSUNDEEP
sqlselect
jepaulabi
jepaulabi

Reputation: 17

SQL to Match columns from 2 different tables using a Select statement

I have 2 tables A & B with columns 6 columns in each table. Table A has lesser rows than table B.

I want to write a Select Statement where if the below condition satisfies

-----A.Col1=B.Col1 and A.Col2=B.Col2 and A.Col3=B.Col3 and A.Col4=B.Col4 and A.Col5=B.Col5 and A.Col6=B.Col6-----

if all conditions are satisfied then in a new column say "Match" else "NoMatch"

How do I do that ?

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

I would suggest using exists. If you want a new column in A:

select a.*,
       (case when exists (select 1
                          from b
                          where A.Col1 = B.Col1 and A.Col2 = B.Col2 and
                                A.Col3 = B.Col3 and A.Col4 = B.Col4 and
                                A.Col5 = B.Col5 and A.Col6 = B.Col6
                         )
             then 'match' else 'nomatch'
        end) as flag
from a;

Note: If you want the new column on B, the logic is the same but the two tables are reversed.

If any of the columns can have NULL values, then you need to take that into account.

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You can use CASE statement and I am considering that you need all data from tableB and matching data from tableA as follows:

select b.*, a.*,
       case when A.Col1=B.Col1 and A.Col2=B.Col2 
             and A.Col3=B.Col3 and A.Col4=B.Col4 
             and A.Col5=B.Col5 and A.Col6=B.Col6
            then 'Match'  
            else 'No match'    
       end as res
  from tableB b 
  left join TableA a
    on A.Col1=B.Col1 and A.Col2=B.Col2 
   and A.Col3=B.Col3 and A.Col4=B.Col4 
   and A.Col5=B.Col5 and A.Col6=B.Col6

Upvotes: 1

Related Questions