Oracle CONNECT BY recursive child to parent query, include ultimate parent that self references
I got that result by using WITH clause.
WITH REC_TABLE ( ID, PARENT_ID)AS( --Start WITH SELECT ID, PARENT_ID FROM table WHERE ID='C' UNION ALL --Recursive Block SELECT T.ID, T.PARENT_ID FROM table T JOIN REC_TABLE R ON R.PARENT_ID=T.ID AND R.PARENT_ID!=R.ID --NoCycle rule)SELECT *FROM REC_TABLE;And it seems to work that way too.
select id, parent_idfrom TSTART WITH id = 'C'CONNECT BY id = PRIOR parent_id and parent_id!= prior id;-- ^^^^^^^^^^^^^^^^^^^^-- break cyclesHope it helps.