SELECT when main table contains NULL and you are attempting a JOIN

Transact-SQL
1

Hi,

My Main Table, Lookups and final results look like this:

Combined
Combined934×580 10.9 KB

So basically I want to maintain the NULLs after the SELECT.

2

You can use LEFT OUTER JOIN to keep the null.

3

hi

hope this helps

Another way of doing ( without joins )

create sample data script

drop table if exists #Main

create table #Main ( ID int , Dt date, ID2 int , ID3 int )

insert into #Main select 4 , getdate() ,1 , null
insert into #Main select 5 , getdate()-1 ,null , 3
insert into #Main select 6 , getdate()-2 , 2 , null

select * from #Main

drop table if exists #LooKUp1

create table #LooKUp1 ( ID2 int , Dscr1 varchar(100) )

insert into #LooKUp1 select 1,' abc'
insert into #LooKUp1 select 2,'def'
insert into #LooKUp1 select 3,'xyz'

select * from #LooKUp1

drop table if exists #LooKUp2

create table #LooKUp2 ( ID3 int , Dscr2 varchar(100) )

insert into #LooKUp2 select 1,' 555'
insert into #LooKUp2 select 2,'666'
insert into #LooKUp2 select 3,'777'

select * from #LooKUp2


SELECT
     ID
	 , Dt 
	 , max(case when #Main.ID2 = #LooKUp1.ID2 then Dscr1 end )
	 , max(case when #Main.ID3 = #LooKUp2.ID3 then Dscr2 end)  
FROM
    #Main , #LooKUp1 , #LooKUp2 
GROUP BY
     ID 
	 ,DT 
ORDER BY 
     ID

image