ASMS 2009
SQL: root of a tree stored in a single table
Document Actions
Having a tree stored in a single table makes it tricky to find the actual root of it.
The table contains a number of trees. Each row is related to ONE parent row unless it is the root. Finding the parent row is done just by using the .parent attribute of the current row - but how to find the actual root of that tree when the depth of the tree is not know. Following function in SQL solves the problem:
CREATE FUNCTION root (id INT)
RETURNS int(11)
BEGIN
DECLARE parentID INT DEFAULT id;
DECLARE childID INT DEFAULT id;
WHILE (parentID is not null)
DO
set childID = parentID;
set parentID = (SELECT parent from formobject where formobject.uoid = childID);
END WHILE;
RETURN childID;
END
CREATE FUNCTION root (id INT)
RETURNS int(11)
BEGIN
DECLARE parentID INT DEFAULT id;
DECLARE childID INT DEFAULT id;
WHILE (parentID is not null)
DO
set childID = parentID;
set parentID = (SELECT parent from formobject where formobject.uoid = childID);
END WHILE;
RETURN childID;
END
Created by
zwluxx
Last modified 2008-01-24 04:18 PM
Last modified 2008-01-24 04:18 PM
