An interesting article on conditional joins in sql server:
I have come across a very different peculiar scenario to join a column based on the conditions.
1. If the column value is not null join with the columnA.
2. If the column value is null join with columnB in the same table.
How can we achieve this?
-- creating temporary tables
drop table venkat
create table venkat(id int, name varchar(100))
insert into venkat values(1,'venkat1')
insert into venkat values(2,'venkat2')
insert into venkat values(3,'venkat3')
insert into venkat values(4,'venkat4')
drop table venkat1
create table venkat1(id int,id1 int, name1 varchar(100))
insert into venkat1 values(1,null,'venkat1')
insert into venkat1 values(2,null,'venkat2')
insert into venkat1 values(null,3,'venkat3')
insert into venkat1 values(4,null,'venkat4')
select * from venkat1
select * from venkat
drop table venkat
create table venkat(id int, name varchar(100))
insert into venkat values(1,'venkat1')
insert into venkat values(2,'venkat2')
insert into venkat values(3,'venkat3')
insert into venkat values(4,'venkat4')
drop table venkat1
create table venkat1(id int,id1 int, name1 varchar(100))
insert into venkat1 values(1,null,'venkat1')
insert into venkat1 values(2,null,'venkat2')
insert into venkat1 values(null,3,'venkat3')
insert into venkat1 values(4,null,'venkat4')
select * from venkat1
select * from venkat

-- Option 1
select * from venkat a
inner join venkat1 b on a.id=
case isnull(b.id,0)
when 0 then b.id1
else
b.id
end
select * from venkat a
inner join venkat1 b on a.id=
case isnull(b.id,0)
when 0 then b.id1
else
b.id
end
-- Option2
select * from venkat a
inner join venkat1 b on a.id=isnull(b.id,b.id1)
select * from venkat a
inner join venkat1 b on a.id=isnull(b.id,b.id1)
-- column values
select a.id,a.name,isnull(b.id,b.id1) as TableBID from venkat a
inner join venkat1 b on a.id=isnull(b.id,b.id1)
select a.id,a.name,isnull(b.id,b.id1) as TableBID from venkat a
inner join venkat1 b on a.id=isnull(b.id,b.id1)

Venkatesan Prabu .J
No comments:
Post a Comment