Using the code below, when I use the case statement 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” statement is called for any time there is a matching NODE_ID name. If there is no matching NODE_ID name, the case statement executes as expected.

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