Table A | Table B | Table C | ||
USERID | TESTID | TESTID | ||
TESTID | SEQ | SEQ | ||
GERMCD | MEDCD | |||
RSLTCD | ||||
INSDATE |
WITH A(USERID, TESTID) AS( SELECT '2247', '000001' FROM DUAL UNION ALL SELECT '2345', '000002' FROM DUAL UNION ALL SELECT '2247', '000003' FROM DUAL ), B(TESTID, SEQ, GERMCD) AS( SELECT '000001', '1', 'PAE' FROM DUAL UNION ALL SELECT '000001', '1', 'ABA' FROM DUAL UNION ALL SELECT '000002', '1', 'PAE' FROM DUAL UNION ALL SELECT '000002', '1', 'ABA' FROM DUAL UNION ALL SELECT '000003', '1', 'PAE' FROM DUAL UNION ALL SELECT '000003', '1', 'KOX' FROM DUAL ), C(TESTID, SEQ, MEDCD, RSLTCD, INSDATE) AS( SELECT '000001', '1', 'AMP', '+', '20130101' FROM DUAL UNION ALL SELECT '000001', '1', 'AMB', 'R', '20130101' FROM DUAL UNION ALL SELECT '000001', '1', 'AMK', 'I', '20130101' FROM DUAL UNION ALL SELECT '000001', '1', 'AMS', 'S', '20130101' FROM DUAL UNION ALL SELECT '000002', '1', 'AMR', 'R', '20130101' FROM DUAL UNION ALL SELECT '000002', '1', 'AMP', 'S', '20130201' FROM DUAL UNION ALL SELECT '000002', '1', 'TGC', '-', '20130101' FROM DUAL UNION ALL SELECT '000003', '1', 'AMP', 'R', '20130301' FROM DUAL UNION ALL SELECT '000003', '1', 'VAN', 'I', '20130301' FROM DUAL ) SELECT A.USERID, B.GERMCD, C.MEDCD, C.RSLTCD, C.INSDATE FROM A INNER JOIN B ON A.TESTID = B.TESTID INNER JOIN C ON B.TESTID = C.TESTID AND B.SEQ = C.SEQ WHERE C.INSDATE BETWEEN '20130101' AND '20130301' AND B.GERMCD = 'PAE' AND C.MEDCD = 'AMP' AND A.USERID = '2247' ; 위 쿼리로 결과를 구하면, userid germcd medcd rlstcd insdate 2247 PAE AMP + 20130101 2247 PAE AMP R 20130301 이렇게 나오는데요. 제가 원하는 결과는userid germcd medcd rlstcd insdate 2247 PAE AMP + 20130101 이렇게 조회기간 내에서 유저별, 세균별, 약품별로 가장 첫번째 결과만 보는 것입니다.