DROP TABLE Stack; CREATE TABLE Stack (stack_top INTEGER NOT NULL, child VARCHAR(10) NOT NULL, lft INTEGER NOT NULL, rgt INTEGER); -- you can create optional indexes on stack_top and child columns BEGIN DECLARE @lft_rgt INTEGER, @stack_pointer INTEGER, @max_lft_rgt INTEGER; SET @max_lft_rgt = 2 * (SELECT COUNT(*) FROM Tree); INSERT INTO Stack SELECT 1, child, 1, @max_lft_rgt FROM Tree WHERE parent IS NULL; SET @lft_rgt = 2; SET @Stack_pointer = 1; DELETE FROM Tree WHERE parent IS NULL; -- The Stack is now loaded and ready to use WHILE (@lft_rgt < @max_lft_rgt) BEGIN IF EXISTS (SELECT * FROM Stack AS S1, Tree AS T1 WHERE S1.child = T1.parent AND S1.stack_top = @stack_pointer) BEGIN -- push when stack_top has subordinates and set lft value INSERT INTO Stack SELECT (@stack_pointer + 1), MIN(T1.child), @lft_rgt, NULL FROM Stack AS S1, Tree AS T1 WHERE S1.child = T1.parent AND S1.stack_top = @stack_pointer; -- remove this row from Tree DELETE FROM Tree WHERE child = (SELECT child FROM Stack WHERE stack_top = @stack_pointer + 1); SET @stack_pointer = @stack_pointer + 1; END -- push ELSE BEGIN -- pop the Stack and set rgt value UPDATE Stack SET rgt = @lft_rgt, stack_top = -stack_top WHERE stack_top = @stack_pointer SET @stack_pointer = @stack_pointer - 1; END; -- pop SET @lft_rgt = @lft_rgt + 1; END; -- if END; -- while SELECT * FROM Stack ORDER BY lft; Stack stack_top emp lft rgt ----------------------------- -1 Albert 1 12 -2 Bert 2 3 -2 Chuck 4 11 -3 Donna 5 6 -3 Eddie 7 8 -3 Fred 9 10