SQL table join changes case aggregate expression logic

Question

Using the code below, when I use the case expression without joining tables together, I have no issue.

select t1.PNODE_ID, --t2.NODE_ID,
case
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP26,AS_SP15' then 'SP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_SP26' then 'SP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_NP15' then 'NP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP15,AS_NP26' then 'NP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_NP26' then 'ZP26'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_SP15' then 'ZP26'
    else 'Unknown'
end as CLEANED_ZONE
from ATL_AS_REGION_MAP t1
--left join ATL_CBNODE t2 on t1.PNODE_ID = t2.NODE_ID
group by 
t1.PNODE_ID
--t2.NODE_ID

However, when I try to join the tables together, the "else" expression is called for any time there is a matching NODE_ID name. If there is no matching NODE_ID name, the case expression executes as expected.

I would like to understand why this is happening and how to join the tables together with the case expression appropriately.

The case expression is defined off the following column output:

select t1.PNODE_ID, STRING_AGG(t1.AS_REGION_ID, ',') as AGG_STRING, t2.NODE_ID,
case
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP26,AS_SP15' then 'SP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_SP26' then 'SP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_NP15' then 'NP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP15,AS_NP26' then 'NP15'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_SP15,AS_NP26' then 'ZP26'
    when STRING_AGG(t1.AS_REGION_ID, ',') = 'AS_NP26,AS_SP15' then 'ZP26'
    else 'Unknown'
end as CLEANED_ZONE
from ATL_AS_REGION_MAP t1
left join ATL_CBNODE t2 on t1.PNODE_ID = t2.NODE_ID
group by 
t1.PNODE_ID,
t2.NODE_ID

Answer 1

You can resolve these zones in a CTE prior to your left join (the same way you did without the left join, where you found it was working correctly):

;with cte as (
    select 
        ar.PNODE_ID,
        case
            when STRING_AGG(ar.AS_REGION_ID, ',') in ('AS_SP26,AS_SP15','AS_SP15,AS_SP26') then 'SP15'
            when STRING_AGG(ar.AS_REGION_ID, ',') in ('AS_NP26,AS_NP15','AS_NP15,AS_NP26') then 'NP15'
            when STRING_AGG(ar.AS_REGION_ID, ',') in ('AS_SP15,AS_NP26','AS_NP26,AS_SP15') then 'ZP26'
            else 'Unknown'
        end as CLEANED_ZONE
    from ATL_AS_REGION_MAP ar
    group by ar.PNODE_ID
)
select distinct
    cte.PNODE_ID,
    acb.NODE_ID,
    cte.CLEANED_ZONE
from 
    cte
    left join ATL_CBNODE acb 
    on cte.PNODE_ID = acb.NODE_ID;