CODEWITHSUNDEEP
sqlapache-sparkapache-spark-sql
Arsalan
Arsalan

Reputation: 77

Select a new column based on values of two column from two tables with null if no data is present

How to select a new column based on values of two column from two tables with null if no data is present.

I have two tables

table1 with column1 and table2 with column2. I need to select data from both these columns in column3 (column3 would be a part of table1) in a way that:

column1     column2     column3
-------     -------     -------
1           2           null
2           3           present
3                       present
4                       null

If the value of column2 is present in column1

=> I need to be able to assign a string(lets say "present") in column3

else the value in column3 should be null

At present I am using join but I wasnt able to assign the null part to column3

Thanks in advance.

Upvotes: 1

Views: 941

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

 create table table1(column1 int);
 create table table2(column2 int);

 insert into table1 values(1);
 insert into table1 values(2);
 insert into table1 values(3);
 insert into table1 values(4);
 
 insert into table2 values(2);
 insert into table2 values(3);

Query:

 select column1, (case when column2 is not null then 'Present' else Null end) Column3 
 from table1 left join table2 on column1=column2

Output:

column1 Column3
1 null
2 Present
3 Present
4 null

db<>fiddle here

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

if col1 and col2 don't have duplicate value, you can use:

select t1.col1 , case when t2.col2 is not null then 'present' else null end 
from table1 t1
left join table2 t2
on t1.col1 = t2.col2

Upvotes: 0

Related Questions