with t as ( select '1' AS ID, null AS P_ID, 'aa' AS text from dual union all select '11' AS ID, '1' AS P_ID, 'bb' AS text from dual union all select '111' AS ID, '11' AS P_ID, 'cc' AS text from dual ) select * from t where text = 'BB'
위와 비슷한 구조인데...흐음 이것저것 고민해보고 있는데요..
본문의 text나 댓글의 text 대댓글의 text 어느걸로 검색조건을 타도 본문에서 대댓글 전부 다 조회되게 하고 싶은데..
중복이 나거나 뭔가 누락되거나 계속 헤메이네요..;;
도움 부탁드립니다. ㅠ
WITH t AS ( SELECT 1 id, null p_id, 'aa' text FROM dual UNION ALL SELECT 11, 1, 'bb' FROM dual UNION ALL SELECT 111, 11, 'cc' FROM dual UNION ALL SELECT 12, 1, 'bb' FROM dual UNION ALL SELECT 121, 12, 'cc' FROM dual UNION ALL SELECT 13, 1, 'dd' FROM dual UNION ALL SELECT 131, 12, 'ee' FROM dual ) SELECT id , p_id , text , LEVEL lv FROM (SELECT id, p_id, text FROM t START WITH UPPER(text) LIKE UPPER('%'||'BB'||'%') CONNECT BY PRIOR p_id = id UNION SELECT id, p_id, text FROM t START WITH UPPER(text) LIKE UPPER('%'||'BB'||'%') CONNECT BY PRIOR id = p_id ) START WITH p_id IS NULL CONNECT BY PRIOR id = p_id ;