Return node's full parentage in tree data structure

[Nick Mirro]

An evolutionary tree is represented by names in several tables using
NodeNames, NodeIDs and ParentIDs. The link below shows the relationships.

Imagine the tree is upright in this description (parents are down). Each
node in the tree (except the few at the very bottom - trunk portion) has
assigned to it a parent node by the identifier, ParentID. This creates a
sequential hierarchy below each childmost terminal (leaf) node in the tree.

I have 114 genus names and their NodeIDs. I would like to return a list of
all sequential parents (NodeIds and NodeNames) to the very bottom of the
tree for each of the 114 (leafy) genuses. Each should have somewhere
between 15 and 40 ancestors. If someone wouldn't mind helping me, how could
I query or code for these 114 ancestral lists? Thanks.

Nick

http://home.comcast.net/~nickmirro/images/Temporary/Relationships.JPG

 

[Allen Browne]

If you are trying to do this in SQL, Joe Celko has suggestions:
http://www.intelligententerprise.com/001020/celko.shtml

http://www.devx.com/premier/mgznarch/vbpj/2001/07jul01/sqlpro0107/rein0107/rein-1.asp
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

 

[Nick Mirro]

I am not knowledgeable in sql. That page looked perfect but left my head
spinning. It appears that my tables need to be set up in accordance with
his design. Can I ask how I could translate this to return a lineage with
my tables?

1. Find an employee and all his/her supervisors, no matter how deep the
tree.
SELECT P2.*
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp = :myemployee;
My
tables:http://home.comcast.net/~nickmirro/images/Temporary/Relationships.JPG

 

[Adrian Jansen]

I played around with that a bit, in doing similar things with a product
database for assemblies and sub assemblies, decomposing down into individual
components. After a fair bit of work, I decided that it needed a lot of
massaging to get the data into the form used by Joe Celko, and you had to do
a lot of manipulation in code each time the data holding the component
structures changed. Even though his queries are very simple, maintaining
the data structures wasnt. I ended up with the standard single junction
table holding all the structures, and tracing down them in code whenever I
wanted a tree view. I can share some code if you want.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Nick Mirro]

Thanks for you response. This is an important project to me and I would be
grateful for help generating these lineages. Sql or vb are my weakpoints,
but I'll do my best to make sense out of and apply what you wrote. Please
show me what you came up with.

Nick