table1 | ||
itm_id | co_cd | itm_cd |
42 | 10 | a |
9 | 10 | b |
23498 | 10 | c |
24471 | 10 | d |
17907 | 10 | e |
4412 | 10 | f |
4454 | 10 | g |
24564 | 10 | h |
24567 | 10 | i |
24634 | 10 | j |
24635 | 10 | k |
table2 | ||
모품목ID | 자품목ID | |
24471 | 9 | |
24471 | 42 | |
24471 | 4412 | |
24471 | 4454 | |
24471 | 17907 | |
24471 | 23498 | |
24471 | 24564 | |
24471 | 24567 | |
24471 | 24634 | |
24471 | 24635 |
테이블 1번과 2번을 조인걸어서 item_cd 전체를 가져오려고 합니다
2번테이블에 모품목,자품목 둘다 1번테이블 item_id에 조인걸어야되는데
union 말고는 답이 없나요?
조언좀 주십시요 미리 감사합니다
WITH table1 AS ( SELECT 42 itm_id, 10 co_cd, 'a' itm_cd UNION ALL SELECT 9, 10, 'b' UNION ALL SELECT 23498, 10, 'c' UNION ALL SELECT 24471, 10, 'd' UNION ALL SELECT 17907, 10, 'e' UNION ALL SELECT 4412, 10, 'f' UNION ALL SELECT 4454, 10, 'g' UNION ALL SELECT 24564, 10, 'h' UNION ALL SELECT 24567, 10, 'i' UNION ALL SELECT 24634, 10, 'j' UNION ALL SELECT 24635, 10, 'k' ) , table2 AS ( SELECT 24471 모품목ID, 9 자품목ID UNION ALL SELECT 24471, 42 UNION ALL SELECT 24471, 4412 UNION ALL SELECT 24471, 4454 UNION ALL SELECT 24471, 17907 UNION ALL SELECT 24471, 23498 UNION ALL SELECT 24471, 24564 UNION ALL SELECT 24471, 24567 UNION ALL SELECT 24471, 24634 UNION ALL SELECT 24471, 24635 ) SELECT a.모품목ID , a.자품목ID , b.itm_cd 모품목CD , c.itm_cd 자품목CD FROM table2 a INNER JOIN table1 b ON b.co_cd = 10 AND b.itm_id = a.모품목ID INNER JOIN table1 c ON c.co_cd = 10 AND c.itm_id = a.자품목ID ;