Join condition logic when two possible matches but only want one

Question

First, i know that my title stinks. Did not know how to properly phrase my question.

i have a code table that i want to pull out the description. I match on code_name and code. But there is a third column, tenant_cd, that describes the type of tenant. Different tenants can have different descriptions. My BA is using a blank tenant_cd as a catch all. If there is a tenant_cd match, then use that description. But if there is not a tenant code match then use the description with the blank tenant_cd. Right now I have an OR condition in my Left Join. But that is causing duplicates. It is like i want to rank my tenant_cd join condition with matching #1 and if not matching then code

this is what i want for the results

create table codes (column_name varchar(50), code varchar(50), code_desc varchar(50), TENANT_CD varchar(50));  
  
insert into codes values ('cas_sta', 'A1', 'FIS CLOSED', 'FIS_TENANT'), ('cas_sta', 'A1', 'COOP CLOSED', 'COOP_TENANT'), ('cas_sta', 'A1', 'OTHER CLOSED', ''),   
						('cas_sta', 'A2', 'ALL CLOSED', '')   
  
  
drop table dashboard1_table  
create table dashboard1_table (acct_nbr varchar(50), cas_sta varchar(50), TENANT_CD varchar(50));  
  
insert into dashboard1_table values ('111', NULL, ''),   
									('222', 'A1','FIS_TENANT'),   
									('223', 'A1','COOP_TENANT'),  
									('224', 'A1','TENANT XYZ'),    
									('333', 'A2','FIS_TENANT'),   
									('444', 'A2','COOP_TENANT'),   
									('555', 'A0', '');  
  
select  
    d.acct_nbr,  
	d.TENANT_CD,  
    d.cas_sta,  
    case when c1.code_desc is NULL then 'UNKNOWN' else c1.code_desc end as CAS_STA_DESC  
    from dashboard1_table d  
       left join codes c1 on c1.column_name = 'cas_sta' and c1.code = d.cas_sta and (d.tenant_cd = c1.tenant_cd or c1.tenant_cd = '')   

Answer 1

Here is a solution using OUTER APPLY with TOP 1:

select
    d.acct_nbr,
    d.tenant_cd,
    d.cas_sta,
    case when c1.code_desc is NULL then 'UNKNOWN' else c1.code_desc end as CAS_STA_DESC
    from dashboard1_table d
    outer apply (select top (1) c1.code_desc
                 from   codes c1
                 where  c1.column_name = 'cas_sta' 
                  and   c1.code = d.cas_sta 
                  and   (d.tenant_cd = c1.tenant_cd or c1.tenant_cd = '') 
                 order by case when d.tenant_cd = c1.tenant_cd then 0 else 1 end) AS c1

Answer 2

Check if this query works with all of your data:

select
    d.acct_nbr,
    d.TENANT_CD,
    isnull(d.cas_sta, '') as cas_sta,
    isnull(c1.code_desc, 'UNKNOWN') as CAS_STA_DESC
from dashboard1_table d
left join codes c1 on c1.column_name = 'cas_sta' and c1.code = d.cas_sta and c1.tenant_cd = d.tenant_cd
left join codes c2 on c2.column_name = 'cas_sta' and c2.code = d.cas_sta and c2.tenant_cd = ''
order by acct_nbr

Answer 3

Hi @brenda grossnickle ,

Welcome to Microsoft Q&A!

Please also refer below query using ROW_NUMBER():

select acct_nbr,TENANT_CD,cas_sta,CAS_STA_DESC from (  
select d.acct_nbr,d.TENANT_CD,d.cas_sta,  
case when c1.code_desc is NULL then 'UNKNOWN' else c1.code_desc end as CAS_STA_DESC  
,ROW_NUMBER() over (partition by d.acct_nbr,d.TENANT_CD,d.cas_sta order by isnull(c1.tenant_cd,'') desc ) rn  
from dashboard1_table d  
left join codes c1   
on c1.column_name = 'cas_sta'   
and c1.code = d.cas_sta and   
(d.tenant_cd = c1.tenant_cd or c1.tenant_cd = '')) a  
where rn=1  

Output:

acct_nbr	TENANT_CD	cas_sta	CAS_STA_DESC  
111		NULL	UNKNOWN  
222	FIS_TENANT	A1	FIS CLOSED  
223	COOP_TENANT	A1	COOP CLOSED  
224	TENANT XYZ	A1	OTHER CLOSED  
333	FIS_TENANT	A2	ALL CLOSED  
444	COOP_TENANT	A2	ALL CLOSED  
555		A0	UNKNOWN  

If above is not working, please provide more sample data and expected output. Thanks.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.