SQL to return all descendants

[Erick]

I have a table with 2 fields 'Elements' & 'Parent'. These 2 fields represent a hierarchial child-parent relationship (like treeview).
eg.

Element ----Parent
1
1.1 -----------1
1.2------------2
1.1.1----------1.1
1.1.1.1--------1.1.1
The depth of the hierarchy is variable. I need a query that will identify all the descendants of any particular element, so for element 1.1 all the descendants are 1.1.1 & 1.1.1.1.

I can probably get this result using some kind of look up loop in vba but I am hoping that an SQL query can return the same thing.

 

[jackd]

Most google searches for information on this type of SQL/query end up by pointing you to
Joe Celko and a method to deal with hierarchies.

Here is one link - it's MySQl related but the SQL may be helpful.
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Good luck.