꿈꾸는 개발자, DBA 커뮤니티 구루비

[해결]값과 컬럼의 수 유형이 다른 두 개의 테이블 조인시 조건..(후보키 존재)

by 훈마로 [SQL Query] 쿼리 조인 UNION JOIN WHERE 조건 [2016.02.18 15:15:47]

다음 세 개의 테이블이 있습니다.

[USER]

USER_ID(PK)	USER_NAME	DEPT_ID	DEPT_NAME
lee1	이일	100	정치부
lee2	이이	200	역사부
lee3	이삼	300	경제부
lee4	이사	100	정치부
lee5	이오	200	역사부
lee6	이륙	600	과학부

[DEPT]

DEPT_ID	DEPT_NAME	DEPT_ORDER
100	정치부	1
200	역사부	2
300	경제부	3
400	수학부	4
500	체육부	5
600	과학부	6

[TEST_RESULT]

TEST_SEQ	RESULT_SEQ	USER_ID	USER_NAME	DEPT_ID	DEPT_NAME	TOTAL_SCORE
1	1	lee1	이일	100	정치부	90
1	2	lee6	이륙	600	과학부	80
2	1	lee1	이일	100	정치부	90
3	1	lee4	이사	100	정치부	100
1	3	lee5	이오	200	역사부	70
2	2	lee2	이이	200	역사부	80

결과로 바라는 것은

특정 시험(TEST_SEQ) 참여현황 페이지 모든 USER를 뿌려주면서 참여 여부(JOIN_YN)을 보여주는 것입니다.

그리고 검색 조건에 따라 리스트를 다시 뿌려주는 것입니다.

현재 제가 짠 SQL은 다음과 같습니다.

SELECT
    ROW_NUMBER() OVER(ORDER BY DEPT_ORDER ASC) RN,
    TEST_SEQ,
    RESULT_SEQ,
    DEPT_ID,
    DEPT_NAME,
    USER_ID,
    USER_NAME,
    NVL(JOIN_YN, 'N') AS JOIN_YN,
    TOTAL_SCORE
FROM
(SELECT
    NULL AS TEST_SEQ,
    NULL AS RESULT_SEQ,
    D.DEPT_ID,
    D.DEPT_NAME,
    D.DEPT_ORDER,
    U.USER_ID,
    U.USER_NAME,
    "" AS JOIN_YN,
    NULL AS TOTAL_SCORE
FROM USER U, DEPT D
WHERE U.USER_STAT = 'A'
AND D.DEPT_ID = U.DEPT_ID
AND NOT EXISTS
    (
        SELECT 1
        FROM TEST_RESULT X
        WHERE U.USER_ID = X.USER_ID
     )
UNION
SELECT
    T.TEST_SEQ,
    T.RESULT_SEQ,
    T.USER_ID,
    T.USER_NAME,
    D.DEPT_ID,
    D.DEPT_NAME,
    D.DEPT_ORDER,
    DECODE(U.USER_ID, T.USER_ID, 'Y', 'N') AS JOIN_YN,
    T.TOTAL_SCORE
FROM TEST_RESULT T, USER U, DEPT D
WHERE T.USER_ID = U.USER_ID
AND T.DEPT_ID = D.DEPT_ID
)
WHERE TEST_SEQ = 1 OR TEST IS NULL

여기까지가 기본 쿼리구요

페이지에서 부서나 사용자, 참여여부 등으로 검색을 할 경우 전체쿼리 뒤에 AND로 쿼리를 덧 붙이는 방식입니다.

근데 결과가 기대와 달리 나오네요.

[기본 쿼리 결과 (TEST_SEQ = 1)]

TEST_SEQ	RESULT_SEQ	USER_ID	USER_NAME	DEPT_ID	DEPT_NAME	JOIN_YN	TOTAL_SCORE
1	1	lee1	이일	100	정치부	Y	90
		lee4	이사	100	정치부	N
1	3	lee5	이오	200	역사부	Y	70
		lee2	이이	200	역사부	N
		lee3	이삼	300	경제부	N
1	2	lee6	이륙	600	과학부	Y	80

결과: 기존 검색조건(TEST_SEQ = 1)에 (AND DEPT_ID = '100') 추가시

TEST_SEQ	RESULT_SEQ	USER_ID	USER_NAME	DEPT_ID	DEPT_NAME	JOIN_YN	TOTAL_SCORE
1	1	lee1	이일	100	정치부	Y	90
		lee4	이사	100	정치부
1	3	lee5	이오	200	역사부	Y	70
1	2	lee6	이륙	600	과학부	Y	80

결과는 첫번째 줄만 나와야 하는데, DEPT_ID 검색 조건이 제대로 안 먹히네요..

UNION을 썼는데 제가 놓치는 부분이 있는 것 같기도 하고...

지금 해결책은 A UNION B 상태에서 A, B 쿼리에 똑깥이 검색 조건을 걸어야 합니다.

쿼리와 서버단 코딩이 같은 작업을 두 번 해야하니 지저분해지네요.

해결 법좀 알려주세요..ㅜㅜ

by 훈마로 [2016.02.18 15:36:53]

해결했네요..................... 검색조건 안 묶어줘서 그렇네요 허허

by 창조의날개 [2016.02.18 15:46:23]

UNION 안써도 될거 같은데요..

그리고 구지 쓴다면 UNION ALL로 하셔도 무방할 듯 합니다.

SELECT
    T.TEST_SEQ,
    T.RESULT_SEQ,
    U.USER_ID,
    U.USER_NAME,
    D.DEPT_ID,
    D.DEPT_NAME,
    D.DEPT_ORDER,
    DECODE(T.USER_ID, NULL, 'N', 'Y') AS JOIN_YN,
    T.TOTAL_SCORE
FROM USER U, DEPT D, TEST_RESULT T
WHERE U.DEPT_ID = D.DEPT_ID
  AND U.USER_ID = T.USER_ID(+)
;

by 훈마로 [2016.02.18 16:25:26]

무조건 조건에 TEST_SEQ를 걸어서 특정 시험에 들어가야하고
그 후에 조건이 걸려야 합니다.

by 창조의날개 [2016.02.18 17:00:19]


--[기본 쿼리 결과 (TEST_SEQ = 1)]
SELECT
    T.TEST_SEQ,
    T.RESULT_SEQ,
    U.USER_ID,
    U.USER_NAME,
    D.DEPT_ID,
    D.DEPT_NAME,
    D.DEPT_ORDER,
    DECODE(T.USER_ID, NULL, 'N', 'Y') AS JOIN_YN,
    T.TOTAL_SCORE
FROM USER U, DEPT D, TEST_RESULT T
WHERE U.DEPT_ID = D.DEPT_ID
  AND U.USER_ID = T.USER_ID(+)
  AND (T.TEST_SEQ = 1 OR T.TEST_SEQ IS NULL)
;

--기존 검색조건(TEST_SEQ = 1)에 (AND DEPT_ID = '100') 추가시
SELECT
    T.TEST_SEQ,
    T.RESULT_SEQ,
    U.USER_ID,
    U.USER_NAME,
    D.DEPT_ID,
    D.DEPT_NAME,
    D.DEPT_ORDER,
    DECODE(T.USER_ID, NULL, 'N', 'Y') AS JOIN_YN,
    T.TOTAL_SCORE
FROM USER U, DEPT D, TEST_RESULT T
WHERE U.DEPT_ID = D.DEPT_ID
  AND U.USER_ID = T.USER_ID(+)
  AND (T.TEST_SEQ = 1 OR T.TEST_SEQ IS NULL)
  AND D.DEPT_ID = '100'
;

by 훈마로 [2016.02.18 17:17:08]

오 결과 잘 나오네요 감사합니다.

근데 실제로는 또 중요한 조건이 하나 더 있는데 이거 해보려니 어렵네요.

USER 테이블에 상태 컬럼(재직, 전출, 퇴직 등..)이 하나 더 있습니다.

그리고 TEST_RESULT는 실제로 TEST 테이블과 관계가 맺어져 있는데요.

TEST 하나당 기간이 잡혀져 있어요.

그 기간 안에 참여한 사람은 TEST_RESULT에 들어가는데

나중에 위의 쿼리를 통해 참여 현황을 조회할 때 USER의 상태가 재직 중이 아니어도 TEST_RESULT에 존재하는 USER이면 리스트에 떠야되요.

단순히 AND U.STAT = '재직' 으로 하면 TEST_RESULT에서도 걸러지게 되더라구요.

처음에 그래서 조인으로 안하고 UNION을 썼었던 건데.. 현재 쿼리 구조에서도 가능할까요?

by 마농 [2016.02.19 13:08:26]

SELECT t.test_seq
     , t.result_seq
     , u.user_id
     , u.user_name
     , u.dept_id
     , d.dept_name
     , t.total_score
  FROM user u
     , dept d
     , test_result t
 WHERE u.dept_id = d.dept_id
   AND u.user_stat = 'A'
   AND u.dept_id = 100
   AND u.user_id = t.user_id(+)
   AND t.test_seq(+) = 1
 ORDER BY u.dept_id
     , t.test_seq
     , t.result_seq
     , u.user_id
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변