CREATE TABLE NestedSet (
node CHAR(1) NOT NULL PRIMARY KEY,
lf INT NOT NULL,
rg INT NOT NULL);
INSERT INTO NestedSet VALUES ('A', 1, 8);
INSERT INTO NestedSet VALUES ('B', 2, 3);
INSERT INTO NestedSet VALUES ('C', 4, 7);
INSERT INTO NestedSet VALUES ('D', 5, 6);
CREATE TABLE AdjacencyList (
node CHAR(1) NOT NULL PRIMARY KEY,
parent CHAR(1) NULL);
INSERT INTO AdjacencyList
SELECT A.node,
B.node AS parent
FROM NestedSet AS A
LEFT OUTER JOIN NestedSet AS B
ON B.lf = (SELECT MAX(C.lf)
FROM NestedSet AS C
WHERE A.lf > C.lf
AND A.lf < C.rg);
-- Results
node parent
------ --------
A NULL
B A
C A
D C
Additional resources:
Book: "Trees and Hierarchies in SQL for Smarties" by Joe Celko
Adjacency List Model
http://www.sqlsummit.com/AdjacencyList.htm
Trees in SQL
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=1266295
Labels: adjacency list, hierarchy, nested set, t-sql programming, trees