데이터베이스 질문과 답변
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'
;
) 버튼을 클릭하여 작성 하시면 됩니다.