Hello Sergio,
> hello!! I have this situation, and don't know how to solve it...
>
> My table:
>
> ID ORIGINAL_ID
> ====================
> 1 NULL
> 2 1
> 3 2
> 4 NULL
> 5 1
>
>
> So, the ID 1 has two children (2 and 5) and 2 has one children (3). Of
> course, ID 3 could have N children and so on...
>
> What I need is to select all the descendants of a given record. For
> instance, I want to select all the records related to ID 1, I would have:
>
> ID ORIGINAL_ID
> ====================
> 1 NULL
> 2 1
> 3 2
> 5 1
>
> It sounds that I should use recursion, but I can't figure out how...
If you are using Firebird 2.1 or later, then the easiest way is to use a
recursive common table expression. I hope I got it right, but I think
you get the idea:
with recursive r(id, original_id, hierarchy) as (
select
id
, original_id
, 1 as hierarchy
from region
where id = 1
union all
select
r2.id
, r2.original_id
, r.hierarchy + 1
from region r2, r
where r.id = r2.original_id
)
select * from r;
--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist
http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/
