>> Select b2.ID from BD_Locality b2 >> join lc l on b2.Parent_ID = l.id > >I'm a little confused with the meaning of this join :)
In your recursive CTE, you were selecting ID without specifying whether it was lc.ID or BD_Locality.ID. Theoretically, it has to be the latter, since the prior would give you an infinite loop selecting the same value for every iteration! My point was just that I think it is better to explicitly state what you want to select than to just hope for Firebird to choose the right one. I'd be surprised if Firebird didn't bark (i.e. give a syntax error) at you if you didn't specify this, but I guess it could figure out that you probably wanted to select from the original table rather than the previous record of the CTE whenever there was a conflict. As for the meaning of CTEs, it generally goes like this: <CTE> as SELECT Ancestor.<ID> FROM <PERSONS> Ancestor WHERE Ancestor.<ParentID> IS NULL /*or similar*/ UNION ALL SELECT Child.<ID> FROM <CTE> MyParent JOIN <PERSONS> Child ON MyParent.<ID> = Child.<ParentID> Of course, it is also possible to traverse the other way or use more complex schemes. Set
