CODEWITHSUNDEEP
sqlmatch
Arun.K
Arun.K

Reputation: 103

Compare two tables and select records based on two conditions - not exists and no match

I have two tables and need to compare based on the first and last name. The scenario is like that name exists in one but not in other. The other scenario is name exists but not matching because of typo error etc. I can get non matching records using NOT EXISTS but how will i display the name thats in table2 also? My output table should have three columns like below -

SL.No---Not Exists in Table2--- Name in Table2

Any suggestions appreciated.

Arun

Upvotes: 0

Views: 1360

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24410

select a.firstname
, b.firstname
, a.lastname
, b.lastname
, case 
    when a.firstname is null then 'B only'
    when b.firstname is null then 'A only'
    else 'both'
end
from TableA a
full outer join TableB b
    on b.firstname = a.firstname
    and b.lastname = a.lastname
order by coalesce (a.firstname, b.firstname)
, coalesce (a.lastname, b.lastname)

Upvotes: 1

Related Questions