How to bypass NULL in a Lookup Table using JOIN?

In SQL (SSMS), I am trying to do a lookup using a ranging table (similar to Point_Lookup below) and what I need is to bypass the NULL scenario.

Firstly, please use below SQL codes to replicate the scenario in question:

-- Code for People Data --
Create table #People ([Name] varchar(50) null,Age int null)

Insert into #People VALUES
('George' , 30),
('Clooney' , 18),
('Sandra' , 44),
('Bullock' , 15),
('Adam' , 100)

-- Code for Point_Lookup Data--
Create table #Point_Lookup ([Lower_Limit] int null, [Upper_Limit] int null, [Point] int null)

Insert into #Point_Lookup VALUES
(0, 10, 1),
(10, 20, 2),
(20, 30, 3),
(30, 40, 4),
(40, 50, 5),
(50, NULL, 6)

I have tried below code to successfully join both tables and get the desired points EXCEPT when [Age] >= 50 (Since the Upper_Limit is showing NULL, the point from the lookup table is also showing NULL – desired result should be 6).

Select ppl.*, point.[Point]
from #People as ppl
left join #Point_Lookup as point 
on ppl.[Age] >= point.[Lower_Limit] and 
ppl.[Age] < point.[Upper_Limit]

I have also tried replacing the NULL using ISNULL() but I realized this still does not JOIN both tables when [Age] >= 50 (not quite sure why).

Select ppl.*, point.[Point]
from #People as ppl
left join #Point_Lookup as point 
on ppl.[Age] >= point.[Lower_Limit] 
and ppl.[Age] < isnull(point.[Upper_Limit], point.[Upper_Limit] + 1 + ppl. 
[Age])

Is there a way to somehow only consider one condition –> (ppl.[Age] >= point.[Lower_Limit]) when [Age] >= 50 (without going into the NULL in Upper_Limit)? Maybe somehow using CASE?

The expected result should show 6 Point when [Age] >= 50. Please help.

Answer

You can try using coalesce() function which will work like case when, so if point.[Upper_Limit] is null then it will consider later one

    Select ppl.*, point.[Point]
    from #People as ppl
    left join #Point_Lookup as point 
    on ppl.[Age] >= point.[Lower_Limit] 
    and ppl.[Age] < coalesce(point.[Upper_Limit], point.[Lower_Limit] + 1 + ppl. 
    [Age])

Advertisement

Answer