We help IT Professionals succeed at work.

join based on values

patd1
patd1 asked
on
Using SQL Server 2008 R2.

I want join table a to table b on a.businessKey = b.TMonths.

When b.TMonths >= 120 then it should join to businesskey  =  '>120' on table a;
when  b.TMonthsis null, then it should join to businesskey = '0' on table a;
In all other cases a.businessKey = b.TMonths

How to create this Join?
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Try
 
Select * from a
join b on a.businesskey='>120'
where b.TMonths>=120
UNION ALL
Select * from a
join b on a.businesskey='0'
where b.TMonths IS NULL
UNION ALL
Select * from a
join b on a.businessKey = b.TMonths
where (b.TMonths IS NOT NULL) AND b.TMonths<120

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
< stand back, haven't tried this before... >

SELECT blah, blah, blah
FROM a
   JOIN b ON CASE 
       WHEN ISNULL(b.Tmonth, 0) THEN '0'
       WHEN b.TMonths >= 120 THEN '>120'
       ELSE CAST(b.TMonths as varchar(?)) = a.businesskey

Open in new window

Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
Or try:
 
Select * from
(Select *, case when TMonths>120 then '>120' when TMonths is null then '0' else convert(varchar(20),TMonths) end as BusinessKeyLink
From b) as c
join a when c.BusinessKeyLink = a.businessKey

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
The ISNULL function will transform the NULL values in zero and the UNION (not UNION ALL) will take care of the rest of the rows that the first SELECT filtered (a.businesskey<120):
SELECT A.* 
FROM a
INNER JOIN b ON a.businessKey = ISNULL(b.TMonths,0)
WHERE b.TMonths<120
UNION 
SELECT a.* 
FROM a

Open in new window