CODEWITHSUNDEEP
sqlsql-servert-sqlcasting
Emre Kadan
Emre Kadan

Reputation: 49

How to join two table with CAST in SQL Server

I have two tables and I want to create a view to join them.

IMAGE

I created a picture to explain my problem. As you can see in picture above, values in table 1 are unique and I want to know that if a value in table 1 exists in table 2 or not. I would like to add a column that contain "NO" If it doesn't exist, if it does additional column must contain "YES".

I hope I could explain myself.

Upvotes: 1

Views: 1689

Answers (4)

Deadsheep39
Deadsheep39

Reputation: 611

You can check both sides with full join:

create view dbo.MyViewOfMissingValues
as
select 
    isnull(t1.col, t2.col) col, 
    case 
        when t2.col is null then 'No (missing in t2)' 
        when t1.col is null then 'No (missing in t1)' 
        else 'Yes' -- contained in both tables
    end col_status
from table1 t1
full join table2 t2
    on t1.col = t2.col
group isnull(t1.col, t2.col);

Upvotes: 0

Søren Kongstad
Søren Kongstad

Reputation: 1440

;with A as
(select v from (values ('a'),('b'),('c'),('d'),('e')) v(v))
, B as
(select v from (values ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('d')) v(v))
-- This is where the magic happens
select
distinct a.v,case when b.v is null then 'NO' else 'YES' end existsinb
from  A
left join B
on a.v=b.v
order by v

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would do this as:

select t1.*,
       (case when exists (select 1 from table2 t2 where t2.col = t1.col)
             then 'YES'
             else 'NO'
        end) as flag
from table1 t1;

This should be the most efficient way to accomplish this goal. For best performance you want an index on table2(col).

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can left join the table2 with table1 and see if any rows exists.

select t1.col, case when count(t2.col) = 0 then 'No' else 'Yes' end 
from table1 t1
left join table2 t2
on t1.col = t2.col
group t1.col;

Upvotes: 1

Related Questions