안녕하세요.

where 절에서 하나의 컬럼이 여러개의 조건에 일치하는 값을 가져올 때 어떻게 가져와야 할 지 문의 드립니다.

우선 아래 쿼리 첨부합니다.

WITH PLT_MST AS (
    SELECT 'PLT001' AS PLT_CD, '플랫폼1' AS PLT_NM, 'REF_PLT001' AS REF_PLT_CD FROM DUAL
    UNION ALL
    SELECT 'PLT002' AS PLT_CD, '플랫폼2' AS PLT_NM, 'REF_PLT002' AS REF_PLT_CD FROM DUAL
    UNION ALL
    SELECT 'PLT003' AS PLT_CD, '플랫폼3' AS PLT_NM, 'REF_PLT003' AS REF_PLT_CD FROM DUAL
    UNION ALL
    SELECT 'PLT004' AS PLT_CD, '플랫폼4' AS PLT_NM, 'REF_PLT004' AS REF_PLT_CD FROM DUAL
    UNION ALL
    SELECT 'PLT005' AS PLT_CD, '플랫폼5' AS PLT_NM, 'REF_PLT005' AS REF_PLT_CD FROM DUAL
),TG_PLT_MAP AS (
    SELECT 'TG001' AS TG_CD, 'PLT001' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG001' AS TG_CD, 'PLT002' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG001' AS TG_CD, 'PLT003' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG002' AS TG_CD, 'PLT004' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG003' AS TG_CD, 'PLT005' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG004' AS TG_CD, 'PLT001' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG005' AS TG_CD, 'PLT002' AS PLT_CD  FROM DUAL
), TG_MST AS (
    SELECT 'TG001' AS TG_CD, '타겟1' AS TG_NM FROM DUAL
    UNION ALL
    SELECT 'TG002' AS TG_CD, '타겟2' AS TG_NM FROM DUAL
    UNION ALL
    SELECT 'TG003' AS TG_CD, '타겟3' AS TG_NM FROM DUAL
    UNION ALL
    SELECT 'TG004' AS TG_CD, '타겟4' AS TG_NM FROM DUAL
    UNION ALL
    SELECT 'TG005' AS TG_CD, '타겟5' AS TG_NM FROM DUAL
)
SELECT
    TPM.TG_CD
FROM TG_PLT_MAP TPM
INNER JOIN PLT_MST TM ON TM.PLT_CD = TPM.PLT_CD
WHERE 1=1
AND TM.REF_PLT_CD = 'REF_PLT001'
AND TM.REF_PLT_CD = 'REF_PLT002'
AND TM.REF_PLT_CD = 'REF_PLT003'
;

목표 :  TM.REF_PLT_CD 컬럼이 입력받은 여러개의 값을 전부 만족 시킬 때 TPM.TG_CD의 값을 가져오고 싶음

예를 들어서 TG_PLT_MAP(맵핑테이블)에 들어가 있는 값 중에서 PLT_MST(마스터테이블)에 있는 어떤 참조값을 가진 컬럼 TM.REF_PLT_CD 의 값이 'REF_PLT001' 이고 'REF_PLT002' 이고 'REF_PLT003' 인 항목을 가져오고 싶습니다.

답은 TG_PLT_MAP에 있는 TG_CD가 TG0001인 항목이어야 하는데 위 쿼리처럼 AND 조건에 계속 이어 붙이면 원하는 값이 나오지 않습니다.

그래서 편법 적으로 아래와 같이 쿼리를 짜서 처리를 했습니다. 더 좋은 방법이 있을지 문의 드립니다.

WITH PLT_MST AS (
    SELECT 'PLT001' AS PLT_CD, '플랫폼1' AS PLT_NM, 'REF_PLT001' AS REF_PLT_CD FROM DUAL
    UNION ALL
    SELECT 'PLT002' AS PLT_CD, '플랫폼2' AS PLT_NM, 'REF_PLT002' AS REF_PLT_CD FROM DUAL
    UNION ALL
    SELECT 'PLT003' AS PLT_CD, '플랫폼3' AS PLT_NM, 'REF_PLT003' AS REF_PLT_CD FROM DUAL
    UNION ALL
    SELECT 'PLT004' AS PLT_CD, '플랫폼4' AS PLT_NM, 'REF_PLT004' AS REF_PLT_CD FROM DUAL
    UNION ALL
    SELECT 'PLT005' AS PLT_CD, '플랫폼5' AS PLT_NM, 'REF_PLT005' AS REF_PLT_CD FROM DUAL
),TG_PLT_MAP AS (
    SELECT 'TG001' AS TG_CD, 'PLT001' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG001' AS TG_CD, 'PLT002' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG001' AS TG_CD, 'PLT003' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG002' AS TG_CD, 'PLT004' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG003' AS TG_CD, 'PLT005' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG004' AS TG_CD, 'PLT001' AS PLT_CD  FROM DUAL
    UNION ALL
    SELECT 'TG005' AS TG_CD, 'PLT002' AS PLT_CD  FROM DUAL
), TG_MST AS (
    SELECT 'TG001' AS TG_CD, '타겟1' AS TG_NM FROM DUAL
    UNION ALL
    SELECT 'TG002' AS TG_CD, '타겟2' AS TG_NM FROM DUAL
    UNION ALL
    SELECT 'TG003' AS TG_CD, '타겟3' AS TG_NM FROM DUAL
    UNION ALL
    SELECT 'TG004' AS TG_CD, '타겟4' AS TG_NM FROM DUAL
    UNION ALL
    SELECT 'TG005' AS TG_CD, '타겟5' AS TG_NM FROM DUAL
)
SELECT
    T.TG_CD, T.TG_NM
FROM (
         SELECT *
         FROM (
                  WITH TEMP_TABLE AS (
                      SELECT TPM.TG_CD
                      FROM TG_PLT_MAP TPM
                               INNER JOIN PLT_MST TM ON TM.PLT_CD = TPM.PLT_CD
                      WHERE TM.REF_PLT_CD = 'REF_PLT001'
                      UNION ALL
                      SELECT TPM.TG_CD
                      FROM TG_PLT_MAP TPM
                               INNER JOIN PLT_MST TM ON TM.PLT_CD = TPM.PLT_CD
                      WHERE TM.REF_PLT_CD = 'REF_PLT002'
                      UNION ALL
                      SELECT TPM.TG_CD
                      FROM TG_PLT_MAP TPM
                               INNER JOIN PLT_MST TM ON TM.PLT_CD = TPM.PLT_CD
                      WHERE TM.REF_PLT_CD = 'REF_PLT003'
                  )
                  SELECT
                      DISTINCT TT.TG_CD
                      , COUNT(TT.TG_CD) AS CNT
                  FROM TEMP_TABLE TT
                  GROUP BY TG_CD
              )
        WHERE CNT >= 3
     ) P
    INNER JOIN TG_MST T ON P.TG_CD = T.TG_CD
;