조건에 따른 JOIN이 가능할까요? 0 1 2,262

by 옆집누렁이 [Oracle 기초] [2023.09.14 17:55:25]


WITH TORD_CART_INF AS 
(
SELECT '101' AS PROD_NO, '1001' AS TSVR_TP_CD, 'TEST1' AS MBR_NO FROM DUAL UNION ALL
SELECT '102' AS PROD_NO, '1002' AS TSVR_TP_CD, 'TEST1' AS MBR_NO FROM DUAL
)
, TORD_PROD_BAS AS
(
SELECT '1' AS PATR_NO, '101' AS PROD_NO, '1001' AS TSVR_TP_CD FROM DUAL UNION ALL
SELECT '2' AS PATR_NO, '102' AS PROD_NO, '1002' AS TSVR_TP_CD FROM DUAL UNION ALL
SELECT '3' AS PATR_NO, '103' AS PROD_NO, '1003' AS TSVR_TP_CD FROM DUAL
)
, TPROD_PATR_ONR_CERT_INF AS
(
SELECT '1' AS PATR_NO, 'Y' AS CERT_YN FROM DUAL UNION ALL
SELECT '2' AS PATR_NO, 'N' AS CERT_YN FROM DUAL UNION ALL
SELECT '3' AS PATR_NO, 'N' AS CERT_YN FROM DUAL
)
SELECT *
  FROM TORD_CART_INF T01
  JOIN TORD_PROD_BAS T02
    ON T01.TSVR_TP_CD = T02.TSVR_TP_CD
   AND T01.PROD_NO = T02.PROD_NO
  JOIN TPROD_PATR_ONR_CERT_INF T03
    ON T03.PATR_NO = T02.PATR_NO 
   AND T03.CERT_YN = 'Y'
 WHERE T01.MBR_NO = 'TEST1';

안녕하세요. 조건에 따른 JOIN이 가능한지 여쭤보고 싶습니다.

웹 장바구니에서 서비스타입에 따른 탭을 선택시 조회하는 쿼리입니다.

TSVR_TP_CD = '1001' 탭선택 시만 JOIN TPROD_PATR_ONR_CERT_INF T02 <- JOIN쿼리을 타게 하고 싶은데 혹시 가능할까요? TORD_PROD_BAS.TSVR_TP_CD != '1001' 이 아닐때는 조인을 타면 안됩니다

by 마농 [2023.09.14 18:34:36]
SELECT *
  FROM tord_cart_inf t01
 INNER JOIN tord_prod_bas t02
    ON t01.tsvr_tp_cd = t02.tsvr_tp_cd
   AND t01.prod_no    = t02.prod_no
  LEFT OUTER JOIN tprod_patr_onr_cert_inf t03
    ON t03.patr_no    = t02.patr_no 
   AND t03.cert_yn    = 'Y'
   AND t02.tsvr_tp_cd = '1001'
 WHERE t01.mbr_no     = 'TEST1'
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입