처음 쓰셧던 결과가 나오는 쿼리를 묶어서

connect by 절에 조건을 주는게 아니라

where 절에 조건을 주면 되자않을까요?

with T AS(

select '001' ID, '초등학교' menuName, '' parentId from dual union all

select '002' ID, '1학년' menuName, '001' parentId from dual union all

select '003' ID, '1반' menuName, '002' parentId from dual union all

select '004' ID, '2반' menuName, '002' parentId from dual union all

select '005' ID, '3반' menuName, '002' parentId from dual union all

select '006' ID, '2학년' menuName, '001' parentId from dual union all

select '007' ID, '1반' menuName, '006' parentId from dual union all

select '008' ID, '2반' menuName, '006' parentId from dual union all

select '009' ID, '3반' menuName, '006' parentId from dual union all

select '101' ID, '중학교' menuName, '' parentId from dual union all

select '102' ID, '1학년' menuName, '101' parentId from dual union all

select '103' ID, '1반' menuName, '102' parentId from dual union all

select '104' ID, '2반' menuName, '102' parentId from dual union all

select '105' ID, '3반' menuName, '102' parentId from dual

)

select CON.AA||'-'||CON.BB||'-'||CON.CC

from(

select A.menuName AA, B.menuName BB , C.menuName CC

from T A

LEFT OUTER JOIN T B

ON B.parentId = A.ID

LEFT OUTER JOIN T C

ON C.parentId = B.ID

where A.ID in ('001','101')

) CON

where 1=1

and CON.BB = '1학년'

and CON.CC = '1반'

이게 원하는 내용일진 모르겠습니다만;;

요약하면

select

*

from

(

기존 결과쿼리

)

where 해당 컬럼 like '%'||'1학년'||'%'

and 해당 컬럼 like '%'||'1반'||'%'

WITH t AS
(
SELECT '001' id, '초등학교' menuname, '*' 부모id FROM dual
UNION ALL SELECT '002', '1학년' , '001' FROM dual
UNION ALL SELECT '003', '1반'   , '002' FROM dual
UNION ALL SELECT '004', '2반'   , '002' FROM dual
UNION ALL SELECT '005', '3반'   , '002' FROM dual
UNION ALL SELECT '006', '2학년' , '001' FROM dual
UNION ALL SELECT '007', '1반'   , '006' FROM dual
UNION ALL SELECT '008', '2반'   , '006' FROM dual
UNION ALL SELECT '009', '3반'   , '006' FROM dual
UNION ALL SELECT '101', '중학교', '*'   FROM dual
UNION ALL SELECT '102', '1학년' , '101' FROM dual
UNION ALL SELECT '103', '1반'   , '102' FROM dual
UNION ALL SELECT '104', '2반'   , '102' FROM dual
UNION ALL SELECT '105', '3반'   , '102' FROM dual
)
-- 3. 상,하위 검색 자료 합쳐서 계층 쿼리 완성
SELECT SUBSTR(SYS_CONNECT_BY_PATH(menuname, '-'), 2) x
  FROM (-- 1. 상위 자료 검색
        SELECT DISTINCT *
          FROM t
         WHERE LEVEL != 1
         START WITH 부모id = '001'
         CONNECT BY id = PRIOR 부모id
         UNION ALL
        -- 2. 하위자료 검색
        SELECT *
          FROM t
         START WITH 부모id = '001'
         CONNECT BY PRIOR id = 부모id
        )
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH 부모id = '*'
 CONNECT BY PRIOR id = 부모id
;

 정말 감사합니다!! 덕분에 해결되었습니다 ㅠ