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/