CODEWITHSUNDEEP
sqlsql-serverexcelvlookupdata-comparison
slayernoah
slayernoah

Reputation: 4492

SQL Server query with same functionality as Excel VLookup

I have 2 columns of data that I need to compare with each other - Column A and Column B.

Column A:

Steve
Jane
Mary
Peter
Ed
Scott
Ted

Column B:

Peter
Scott
David
Nancy

I need to find out which of the values in column B are also in column A.

Output expected for above sample data:

Peter   TRUE
Scott   TRUE
David   FALSE
Nancy   FALSE

Thanks for all your help!

Upvotes: 4

Views: 70855

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The problem with a left join is that there might be duplicates in table A.

If this is an issue, you can do this:

select b.col, (case when a.val is NULL then 'FALSE' else 'TRUE' end)
from b left outer join
     (select distinct a.val
      from a
     ) a
     on b.col = a.val;

An alternative way of expressing this is using a correlated subquery. This puts all the logic in the select:

select b.col,
       (case when exists (select 1 from a where a.val = b.col)
             then 'TRUE'
             else 'FALSE'
       end)
from b

Upvotes: 5

ExactaBox
ExactaBox

Reputation: 3395

select 
   b.columnb,
   case when a.columna is null then 'FALSE' else 'TRUE' end 

from
   tableb b left outer join
   tablea a on b.columnb = a.columna

Upvotes: 10

Related Questions