SQL带CASE的LEFT JOIN查询新增INNER JOIN获取ORG3.NAME字段问题

修改说明 #

• 在SELECT字段列表新增ORG3.NAME作为返回字段，可按需调整别名
• 在原有WORK.ORGANISATION2表（别名AO）的关联逻辑后，新增INNER JOIN关联STYR.ORG2表（别名ORG3），使用你提供的关联规则
• 额外修正原SQL两处潜在问题：原B表关联条件未指定主表别名易触发字段歧义、原B表子查询SELECT字段与GROUP BY字段不匹配的笔误，均已同步调整

完整修改后SQL #

SELECT
    A.WORKNR AS "WORK-nr", 
    UCASE(A.NAME) AS "NAME" ,
    UCASE(A.ADRESS) AS "adress",
    A.ZIPNR,
    UCASE(A.ZIPADR) AS "zipadress", 
    A.NNR, 
    CASE
        WHEN AO.ORG_REF IS NULL THEN 'OK'
        ELSE 'NO ' 
    END AS "RESULT OF ORG" ,
    CASE
        WHEN B.B = 1 THEN 'OK' 
        ELSE 'COME WITH ANOTHER NNR' 
    END AS COMPARE_RESULT,
    ORG3.NAME AS "ORG_GENOM_NAME" -- 新增返回ORG3的名称字段
FROM
    WORK.WORK_ADRESS A
LEFT JOIN
    (SELECT
         ZIPNR, UCASE(STREET) AS STREET, 
         COUNT(DISTINCT NNR) AS B
     FROM
         WORK.WORK_ADRESS 
     GROUP BY
         ZIPNR, UCASE(STREET)) B ON B.STREET = UCASE(A.STREET) 
                                 AND B.ZIPNR = A.ZIPNR -- 修正原字段歧义问题
LEFT JOIN 
    WORK.ORGANISATION2 AO ON AO.WORK_REF = A.WORK_REF 
                            AND AO.TILL >= CURRENT_DATE
-- 新增ORG3表关联
INNER JOIN STYR.ORG2 ORG3 ON AO.ORG_REF3 = ORG3.ORG_REF

内容的提问来源于stack exchange，提问作者Tick _Tack