Re: Recursive Query
In article <490ce3$aff@news.sdd.hp.com>, Raj Pratha <raj> wrote:
>Is it possible to write a stored procedure that could be used to select all the
>children records of a given parent record, where the both the children and
>parent records are stored in the same table. That is the table has a one to
>many relationship to itself.
Assuming a table family ( parent_id int, child_id int):
create procedure get_child( t_parent_id int)
returning int; define t_child_id int;
define t_grandchild_id int;
foreach
select child_id into t_child_id
from family
where parent = t_parent
return t_child_id with resume;
^^^^^^^^^^^
foreach
execute procedure get_child( t_child_id) into t_grandchild_id
return t_grandchild_id with resume; ^^^^^^^^^^^
end foreach;
end foreach;
end procedure
Of course you should do some checking about cirles in your parent/child relation.
Otherwise you will bring Online DOWN (Stack Overflow!)
Harald