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
CREATE TABLE Tree (
Node int NOT NULL IDENTITY(100, 1),
EmployeeID int NOT NULL,
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.
>> I'm using the adjacency model with stored path. TREES & HIERARCHIES IN SQL? you can get some ideas from it.