CODEWITHSUNDEEP
sqlsql-server
Dylan Reynolds
Dylan Reynolds

Reputation: 11

VLOOKUP FOR SQL

I have two columns of data that needs to be compared with each other:

COLUMN 1:

 People
 Trust
 Corporate

COLUMN 2:

 People    
 People     
 People    
 Company
 Corporate      
 Corporate
 Corporate
 Company
 Trust
 Trust

COLUMN 1 is shorter than COLUMN 2 but is standing data to be compared against COLUMN 2. I want to find what values are in COLUMN 2 that aren't in COLUMN 1.

A result like this is what I'm looking for:

 People      TRUE
 People      TRUE
 People      TRUE
 Company     FALSE
 Corporate   TRUE 
 Corporate   TRUE 
 Corporate   TRUE 
 Company     FALSE
 Trust       TRUE
 Trust       TRUE

These columns are in two different tables. I am using SQL. Any suggestions?

Upvotes: 0

Views: 398

Answers (3)

Rick Wolff
Rick Wolff

Reputation: 777

The other answers are correct, but I've always found it faster to do with the LEFT JOIN: (I don't know if it's my perception or if it really is more performatic)

SELECT 
  T2.COLUMN, 
  CASE WHEN T1.COLUMN IS NULL THEN 'FALSE' ELSE 'TRUE' END EXISTENT
FROM TABLE_2 T2
LEFT OUTER JOIN TABLE_1 T1
ON T2.COLUMN = T1.COLUMN

Upvotes: 2

GuidoG
GuidoG

Reputation: 12059

the answers are all correct but if you want actual true or false in stead of strings you can convert to a bit field :

declare @temp1 table (column1 varchar(50)) 
declare @temp2 table (column1 varchar(50))

insert into @temp1 (column1) values ('people')
insert into @temp1 (column1) values ('trust')
insert into @temp1 (column1) values ('corporate')

insert into @temp2 (column1) values ('People')    
insert into @temp2 (column1) values ('People')     
insert into @temp2 (column1) values ('People')    
insert into @temp2 (column1) values ('Company')
insert into @temp2 (column1) values ('Corporate')      
insert into @temp2 (column1) values ('Corporate')
insert into @temp2 (column1) values ('Corporate')
insert into @temp2 (column1) values ('Company')
insert into @temp2 (column1) values ('Trust')
insert into @temp2 (column1) values ('Trust')

select t2.column1, convert(bit, case when t1.column1 is null then 0 else 1 end)
from   @temp2 t2
  left join @temp1 t1 on t2.column1 = t1.column1

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

Use a CASE WHEN EXISTS

select Column2, 
       case
         when exists (select Column1 from Table1 T1 where T1.Column1 = Column2)
           then 'TRUE'
         else 'FALSE'
from Table2

Upvotes: 2

Related Questions