Hello Sergio,

>> 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;
>
> THANKS Thomas !!! Yes, I use FB 2.5
>
> May I ask you a very big favor? I'd like to undestand how recursive selects are constructed. Could you explaing me a bit the select you made? Or probably there's somewhere in the net a paper about recursion in FB?? I'd really like to andestand this!! It's a very powerfull tool to use!!!

Check out:
http://firebirdsql.org/rlsnotesh/rlsnotes210.html#rnfb210-cte

Once you get used to using common table expressions (CTEs), including
the ability for querying hierarchical data structures, you don't want to
miss that anymore. Without recursive CTEs you would need to use e.g. a
recursive selectable stored procedure.

Btw, if you are really serious about tree structures, hierarchical data
etc., check out Frank's session from the Firebird conference 2011:
http://www.youtube.com/watch?v=xq7MMcpnL30

Hope this helps.

--
With regards,
Thomas Steinmaurer (^TS^)
Firebird Technology Evangelist

http://www.upscene.com/
http://www.firebirdsql.org/en/firebird-foundation/