SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Sunday, June 25, 2006

Traversing a tree hierachy backwards

SQL Apprentice Question
need to be able to traverse or find the all relevant parent nodes of a
tree given a particular node. Im using the adjacency model with stored path.
So if I know a certain employees number, I need to list all their superiors
back to the top of the tree.
I have a table structure similar to the following

Node int NOT NULL IDENTITY(100, 1),
ParentNode int,
EmployeeID int NOT NULL,
Depth tinyint,
Lineage varchar(255) )
The linieage is represented by a the path to the node seperated by a '/'
Node ParentNode EmployeeID Depth Lineage
100 NULL 1001 0 /
101 100 1002 1 /100/
102 101 1003 2 /100/101/
103 102 1004 3 /100/101/102/

So if I have selected employeeid=1004, I want a quick and easy selected to
show all the superiors.

Celko Answers
>> I'm using the adjacency model with stored path. TREES & HIERARCHIES IN SQL? you can get some ideas from it.

No comments: