Query help to get layers of hierarchical tree

[Blu]

Hi!
I have a table called folder, it is a self join table to store folder_id and associated parent_folder_id. Parent_Folder_id is
null when it is the root of the branch ( many branches in the tree).

How can I write a query to list the parent_folder that layer is over three, in this example will be parent_folder_id is 9.

FOLDER_ID PARENT_FOLDER_ID
------------- ------------------------
10 11
11 8
8
3 5
5 7
7 9
9
2 3
3

Thanks in advance.

Blu

[Shestakov]

select * from
(select t.*, level lv
from folder t
connect by prior folder_id = parent_folder_id
start with parent_folder_id = 9)
where lv >= 3
;

[Blu]

Thanks for the query but I cannot hardcode the parent_folder_id (start with parent_folder_id = 9) in the query since there are more one parent_folder in the folder table. I need to have a query to global select the parenet_folder that has the layers more than three.

Blu

[Shestakov]

Change query:

...
start with parent_folder_id is null )
...
-- for example
-- or any where construction that create ur parent_folder_id set.

start with parent_folder_id in (select id from ...) )