question

brendagrossnickle-6913 avatar image
0 Votes"
brendagrossnickle-6913 asked MelissaMa-msft commented

Join condition logic when two possible matches but only want one

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

95645-image.png



 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 = '') 


sql-server-transact-sql
image.png (10.3 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It is like i want to rank my tenant_cd join condition with #1 where d.tenant_cd = c1.tenant_cd and #2 where c1_tenant_cd = ''.

0 Votes 0 ·

Hi @brendagrossnickle-6913,

Could you please validate all the answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Best regards
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered Viorel-1 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @brendagrossnickle-6913,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.