Hi,

Does anyone got a query or a stored proc which will return all children of a given parent in a self referencing table ?
By children I mean, not only the immediate children, but also all children of the children and so on.

PID-CID
------------------
A-B
-B-B1
-B-B2
--B2--B21
--B2--B22
-B-B3
--B3--B31
---B31---B311
A-C
-C-C1
-C-C2
--C2-C21
-C-C3

So using B as PID would produce:
B-B1
B-B2
B2-B21
B2-B22
B-B3
B3-B31
B31-B311
Thus all chidren of B, all grand children of B, all grand-grand-children of B and so on.
I am using Firebird versione 1.5.

Thanks for any help you might provide.