데이터베이스 질문과 답변
master, detail 테이블 2개를 join 후
master테이블기준으로 한건씩만 조회하고 싶은데요.
즉, master테이블 기준으로 detail테이블에 레코드가 존재 하는지
여부를 알고 싶은 sql문장을 작성하고 싶은거죠.
요청2가지]
1. detail이 없더라도 master는 한건이라도 나오게 하기.
detail이 없다는 컬럼 도 필요
2. detail이 없으면 master도 안나오게 하기
보통 제가 했던방법] -(너무 무식한거 같아서 .. 고수분들이 어떻게 하는지 너무 궁금하네요)
위 1번은 : 컬럼목록에 subquery사용하기 (SELECT COUNT(*)
FROM V_AMT K
WHERE K.EMP_NO = M.EMP_NO
AND ROWNUM = 1)
위 2번은 : 조회조건에 아래처럼 추가
AND 1 = (SELECT 1
FROM V_AMT K
WHERE K.EMP_NO = A.EMP_NO
AND ROWNUM = 1 )
WITH V_MAST AS
(SELECT 'A001' AS EMP_NO, '초롱이' AS EMP_NM FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '말뚝이' AS EMP_NM FROM DUAL UNION ALL
SELECT 'C001' AS EMP_NO, '말뚝이' AS EMP_NM FROM DUAL )
, V_AMT AS
(SELECT 'A001' AS EMP_NO, '201901' AS SAL_MM, 1000 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'A001' AS EMP_NO, '201902' AS SAL_MM, 1100 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'A001' AS EMP_NO, '201903' AS SAL_MM, 1200 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201901' AS SAL_MM, 2000 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201902' AS SAL_MM, 2200 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201903' AS SAL_MM, 2400 AS SAL_AMT FROM DUAL)
SELECT M.*, A.*
FROM V_MAST M
, V_AMT A
WHERE M.EMP_NO = A.EMP_NO (+);
WITH V_MAST AS
(SELECT 'A001' AS EMP_NO, '초롱이' AS EMP_NM FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '말뚝이' AS EMP_NM FROM DUAL UNION ALL
SELECT 'C001' AS EMP_NO, '말뚝이' AS EMP_NM FROM DUAL )
, V_AMT AS
(SELECT 'A001' AS EMP_NO, '201901' AS SAL_MM, 1000 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'A001' AS EMP_NO, '201902' AS SAL_MM, 1100 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'A001' AS EMP_NO, '201903' AS SAL_MM, 1200 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201901' AS SAL_MM, 2000 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201902' AS SAL_MM, 2200 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201903' AS SAL_MM, 2400 AS SAL_AMT FROM DUAL)
SELECT M.*, COUNT(A.EMP_NO) AS CNT
FROM V_MAST M
, V_AMT A
WHERE M.EMP_NO = A.EMP_NO (+)
GROUP BY M.EMP_NO, EMP_NM;
SELECT M.*
, (SELECT EMP_NO
FROM V_AMT A
WHERE A.EMP_NO = M.EMP_NO
AND ROWNUM <= 1) A_EMPNO
FROM V_MAST M
;
WITH MASTER AS
(SELECT 'A001' AS EMP_NO, '초롱이' AS EMP_NM FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '말뚝이' AS EMP_NM FROM DUAL UNION ALL
SELECT 'C001' AS EMP_NO, '말뚝이' AS EMP_NM FROM DUAL
)
, DETAIL AS
(SELECT 'A001' AS EMP_NO, '201901' AS SAL_MM, 1000 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'A001' AS EMP_NO, '201902' AS SAL_MM, 1100 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'A001' AS EMP_NO, '201903' AS SAL_MM, 1200 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201901' AS SAL_MM, 2000 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201902' AS SAL_MM, 2200 AS SAL_AMT FROM DUAL UNION ALL
SELECT 'B001' AS EMP_NO, '201903' AS SAL_MM, 2400 AS SAL_AMT FROM DUAL
)
SELECT *
FROM MASTER M
,DETAIL D
WHERE M.EMP_NO = D.EMP_NO(+)
AND NOT EXISTS (SELECT 1
FROM DETAIL D
WHERE M.EMP_NO = D.EMP_NO)
--AND ROWNUM = 1;
--없다면 무조건 1건인지, 정렬방식은?
--AND D.EMP_NO IS NOT NULL;
--두번째 조건일 경우
) 버튼을 클릭하여 작성 하시면 됩니다.