데이터베이스 질문과 답변
안녕하세요.
2개의 테이블을 조인하여 사진에서 처럼 값을 가져오고 싶은데요~
SELECT LEVEL AS NODE_LEVEL,
A.SN AS MENU_CODE,
A.NAME AS MAINNAME,
B.NAME AS SUBNAME
FROM PMS_MENU_MANAGER A
LEFT OUTER JOIN
(SELECT NAME, MENU_CODE FROM PMS_MENU_MANAGER_VIEW) B
ON A.SN = B.MENU_CODE
START WITH A.PARENT_CODE = 1514
CONNECT BY PRIOR A.SN = A.PARENT_CODE
ORDER SIBLINGS BY A.SEQ;
위와 같이 했을때 원하는 값을 못 가져옵니다.
고수님의 자문 기다리겠습니다......
by 아발란체
[2013.02.14 08:45:10]
샘플 데이타를 올려주세용... ㅋㅅㅋ)/
by 손님
[2013.02.14 14:51:21]
-- 현재
WITH
PMS_MENU_MANAGER AS (
SELECT 1 SEQ,1514 PARENT_CODE,1818 SN,'대쉬보드' NAME FROM DUAL UNION ALL
SELECT 2 SEQ,1514 PARENT_CODE,1819 SN,'상품관리' NAME FROM DUAL UNION ALL
SELECT 3 SEQ,1819 PARENT_CODE,1820 SN,'상품리스트' NAME FROM DUAL UNION ALL
SELECT 4 SEQ,1819 PARENT_CODE,1821 SN,'상품등록' NAME FROM DUAL UNION ALL
SELECT 5 SEQ,1819 PARENT_CODE,1822 SN,'카테고리' NAME FROM DUAL
),
PMS_MENU_MANAGER_VIEW AS (
SELECT 1 SEQ,1818 MENU_CODE,'대쉬보드' NAME FROM DUAL UNION ALL
SELECT 1 SEQ,1820 MENU_CODE,'상품리스트 1' NAME FROM DUAL UNION ALL
SELECT 2 SEQ,1820 MENU_CODE,'상품리스트 2' NAME FROM DUAL UNION ALL
SELECT 3 SEQ,1820 MENU_CODE,'상품리스트 3' NAME FROM DUAL UNION ALL
SELECT 4 SEQ,1820 MENU_CODE,'상품리스트 4' NAME FROM DUAL UNION ALL
SELECT 1 SEQ,1821 MENU_CODE,'상품등록 1' NAME FROM DUAL UNION ALL
SELECT 2 SEQ,1821 MENU_CODE,'상품등록 2' NAME FROM DUAL UNION ALL
SELECT 3 SEQ,1821 MENU_CODE,'상품등록 3' NAME FROM DUAL UNION ALL
SELECT 4 SEQ,1821 MENU_CODE,'상품등록 4' NAME FROM DUAL UNION ALL
SELECT 5 SEQ,1821 MENU_CODE,'상품등록 5' NAME FROM DUAL UNION ALL
SELECT 6 SEQ,1821 MENU_CODE,'상품등록 6' NAME FROM DUAL UNION ALL
SELECT 1 SEQ,1822 MENU_CODE,'카테고리 1' NAME FROM DUAL UNION ALL
SELECT 2 SEQ,1822 MENU_CODE,'카테고리 2' NAME FROM DUAL
)
SELECT LEVEL AS NODE_LEVEL,
A.SN AS MENU_CODE,
A.NAME AS MAINNAME,
B.NAME AS SUBNAME
FROM PMS_MENU_MANAGER A
LEFT OUTER JOIN
(SELECT NAME, MENU_CODE FROM PMS_MENU_MANAGER_VIEW) B
ON A.SN = B.MENU_CODE
START WITH A.PARENT_CODE = 1514
CONNECT BY PRIOR A.SN = A.PARENT_CODE
ORDER SIBLINGS BY A.SEQ;
-- 변정
WITH
PMS_MENU_MANAGER AS (
SELECT 1 SEQ,1514 PARENT_CODE,1818 SN,'대쉬보드' NAME FROM DUAL UNION ALL
SELECT 2 SEQ,1514 PARENT_CODE,1819 SN,'상품관리' NAME FROM DUAL UNION ALL
SELECT 3 SEQ,1819 PARENT_CODE,1820 SN,'상품리스트' NAME FROM DUAL UNION ALL
SELECT 4 SEQ,1819 PARENT_CODE,1821 SN,'상품등록' NAME FROM DUAL UNION ALL
SELECT 5 SEQ,1819 PARENT_CODE,1822 SN,'카테고리' NAME FROM DUAL
),
PMS_MENU_MANAGER_VIEW AS (
SELECT 1 SEQ,1818 MENU_CODE,'대쉬보드' NAME FROM DUAL UNION ALL
SELECT 1 SEQ,1820 MENU_CODE,'상품리스트 1' NAME FROM DUAL UNION ALL
SELECT 2 SEQ,1820 MENU_CODE,'상품리스트 2' NAME FROM DUAL UNION ALL
SELECT 3 SEQ,1820 MENU_CODE,'상품리스트 3' NAME FROM DUAL UNION ALL
SELECT 4 SEQ,1820 MENU_CODE,'상품리스트 4' NAME FROM DUAL UNION ALL
SELECT 1 SEQ,1821 MENU_CODE,'상품등록 1' NAME FROM DUAL UNION ALL
SELECT 2 SEQ,1821 MENU_CODE,'상품등록 2' NAME FROM DUAL UNION ALL
SELECT 3 SEQ,1821 MENU_CODE,'상품등록 3' NAME FROM DUAL UNION ALL
SELECT 4 SEQ,1821 MENU_CODE,'상품등록 4' NAME FROM DUAL UNION ALL
SELECT 5 SEQ,1821 MENU_CODE,'상품등록 5' NAME FROM DUAL UNION ALL
SELECT 6 SEQ,1821 MENU_CODE,'상품등록 6' NAME FROM DUAL UNION ALL
SELECT 1 SEQ,1822 MENU_CODE,'카테고리 1' NAME FROM DUAL UNION ALL
SELECT 2 SEQ,1822 MENU_CODE,'카테고리 2' NAME FROM DUAL
)
SELECT LEVEL NODE_LEVEL
,MENU_CODE
,MENU_NAME
FROM (
SELECT SEQ
,PARENT_CODE
,SN
,SN MENU_CODE
,NAME MENU_NAME
FROM PMS_MENU_MANAGER
UNION ALL
SELECT SEQ
,MENU_CODE PARENT_CODE
,0 SN
,MENU_CODE MENU_CODE
,NAME MENU_NAME
FROM PMS_MENU_MANAGER_VIEW
)
START WITH PARENT_CODE = 1514
CONNECT BY PRIOR SN = PARENT_CODE
ORDER SIBLINGS BY SEQ;
by 정문현
[2013.02.18 10:12:40]
답변주셔서 감사드립니다~^^
즐거운 한주 보내세요~~~*
) 버튼을 클릭하여 작성 하시면 됩니다.