Try this.
WITH rec(id, p_id, scope, cnt)
AS (select folder_id, parent_folder_id, scope, 1000
from folders where my_folder = folder_id
union all
select folder_id, scope, cnt - 1
from rec, folders where rec.p_id = folders.folder_id
and rec.scope = 0
and cnt > 0)
select id from rec where scope = 1;
The code assumes that if the input folder id has a scope of 1 it will be
returned right away.
Also I places a recursion limit of 1000. Not needed, but good style.
Cheers
Serge |