Re: Finding Children in a nonbinary tree-- a recursive puzzle?

[Sam Spritzer <SSpritzer@GW.CTG.COM>]

Kristin...
I am biting my tongue on this one and haven't tried it yet but would it be possible to pass the 'child and find its children' into your WHERE clause?  
Sam

<<< "Love, Kristin" <klove@MSA.COM> 10/ 5  8:52a >>>
I have been working on this puzzle for a while and would like to throw it
open to some larger brains out there....

I am trying to find all the relatives when given a particular unit.

I have a table which looks like this:
 <<...>>

Role =0 means the unit is a parent, role 1 means the unit is a child. So,
for example, when given unit 171054, I want to know that its ancestors were
176040 ("father") and 162281 ("grandfather"). This I can do successfully
with a recursive function:
BEGIN-PROCEDURE FindAncestor(#usn,:#oldest)
BEGIN-SELECT
SerialNo
      show 'Parent         ' $spaces &SerialNo
   Do FindAncestor(&SerialNo,#temp)
   move &SerialNo to #oldest
FROM tablename m
WHERE m.relateid IN ( SELECT relateid
                          FROM tablename
                         WHERE serialno = #usn)
AND role=0
AND m.serialno <> #usn
END-SELECT

END-PROCEDURE FindAncestor

But I seem to be having difficulty going down the tree to find descendants.
The problem is, that when I have a parent with multiple children, the
recursive call only seems to return the set of children, but doesn't take
each child and find its children.

Perhaps this is not the best approach for finding the children. Does anyone
have a fresh idea?

Really, I want to print ALL the relatives on the report, grouped by
relateid. So the report would look like this:

RelateId             SerialNo                 Role
1                      162281                   0
                        170640                   1
........................(more data)
6                      240277                    0
                       240278                     0
                       243341                     1
                       243342                     1

Of course, there would be more info from other tables on this report, like
the unit's name, etc. but I know I need at least this much to start.....

I initially tried to solve this algorithm with connect by, but, as you see,
the unit's parent is not on the table...AND a unit can have multiple
parents.
:) Kristin