Get all ChildID's from a Self Related table?

  • Thread starter Thread starter Codemonkey
  • Start date Start date
C

Codemonkey

Hi,

I've got a table like the following:

ID: AutoNumber
Name : Text
ParentID: Number

The "ParentID" field relates to the ID field to indicate an item that is the
parent of another (The table represents a treeview structure).

Does anybody have any ideas on how to get all the children, grand children,
great grand children etc. based on a ParentID? I'd like to use SQL only and
it shouldn't matter how many levels there is.

Thanks for any help in advance.

Trev.
 
Thanks for the tip, but the example seems to be limited to the number of
times you copy the table in the query.

I'm after something that will return unlimited generations of parent and
child records (Parent, Child, Grandchild, GreatGrandchild,
GreatGreatGrandchild, GreatGreatGreatGrandchild and so on...) I suspect that
some kind of recursive query that ends when no values are returned may be
needed.

Thanks again,

Trev.
 
The programming technique used to return "unlimited generations" in
this kind of situation is called recursion. And even this technique
does not, in practice, work for an unlimited number of generations, as
it must store the layers on a stack to be able to return to them
later. Thus, it has a limitation based on the amount of memory in the
computer. But at least this can be a very high limit.

Before you can approach using this, you should be prepared to
demonstrate that the parent relationship creates a tree structure in
the database. If the database does not contain a single, connected
tree and no cycles (either directed or not) then several bad things
may happen. The worst of these is that the program traversing the
data will get into a loop that would not end unless a way to detect
this situation is also programmed.

The way to avoid having an endless loop when the data is bad can be
just to limit the number of generations allowed. But once you've done
that, you're back to the solution you just rejected - one that works
for a limited number of generations.

If you want, we can discuss how to structure the data to prevent its
not being a tree structure (that is, to avoid errors in data entry
that will only cause invalid results).

Performing a traversal on a recursive basis can be performed using a
VBA module. It would need to create a temporary table showing a
traversal of the tree (pre-, post-, or end-order traversals).

I have looked for, but never found a way to write a query that is
recursive. I seriously don't think this was a design goal of those
who defined the SQL standards. If anyone knows otherwise, I'd really
appreciate hearing of that.

Because of that, a finite generation query system is probably the best
query based solution.

Thanks for the tip, but the example seems to be limited to the number of
times you copy the table in the query.

I'm after something that will return unlimited generations of parent and
child records (Parent, Child, Grandchild, GreatGrandchild,
GreatGreatGrandchild, GreatGreatGreatGrandchild and so on...) I suspect that
some kind of recursive query that ends when no values are returned may be
needed.

Thanks again,

Trev.
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for the help Tom. I guess recursion wasn't in the design of SQL
because of the potential loopholes you mentioned.

Guess I'll have to go for a more conventional method. If I come up with a
viable solution in the meantime, I'll post it here.

Cheers again,

Trev.
 
Back
Top