how to query this table into the result like this?

im not very good at sql but need to query the database to use in my
programming script.
if the database is just like this

id name parent_id
1 A null <-- root
2 B 1
3 C 1
4 D 1
5 E 2
6 F 2
7 G 3
8 H 4
9 I 4
10 J 4
11 K 9

the data in the above table is just like some sort of tree data
which have parent and child node
and now,how to query into the result like this

the deepest node is in level 4

what i want is how deep this tree data is?
anyone show me the query script or store procedure to find the
result please?
many thanks in advance,

--
Posted via http://dbforums.com"per" <member30981@.dbforums.com> wrote in message news:3168898.1059634365@.dbforums.com...
> im not very good at sql but need to query the database to use in my
> programming script.
> if the database is just like this
> id name parent_id
> 1 A null <-- root
> 2 B 1
> 3 C 1
> 4 D 1
> 5 E 2
> 6 F 2
> 7 G 3
> 8 H 4
> 9 I 4
> 10 J 4
> 11 K 9
> the data in the above table is just like some sort of tree data
> which have parent and child node
> and now,how to query into the result like this
> the deepest node is in level 4
> what i want is how deep this tree data is?
> anyone show me the query script or store procedure to find the
> result please?
> many thanks in advance,
> --
> Posted via http://dbforums.com

CREATE TABLE Tree
(
node_id INT NOT NULL PRIMARY KEY,
node_name VARCHAR(10) NOT NULL UNIQUE,
parent_id INT NULL REFERENCES Tree (node_id)
)

-- Sample data
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (1, 'A', NULL)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (2, 'B', 1)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (3, 'C', 1)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (4, 'D', 1)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (5, 'E', 2)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (6, 'F', 2)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (7, 'G', 3)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (8, 'H', 4)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (9, 'I', 4)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (10, 'J', 4)
INSERT INTO Tree (node_id, node_name, parent_id)
VALUES (11, 'K', 9)

-- Returns a table of all descendants of a given node and their level numbers
-- If the given node is NULL, use root node of tree
CREATE FUNCTION Descendants(@.root_node VARCHAR(10) = NULL)
RETURNS @.nodes TABLE
(node_id INT NOT NULL PRIMARY KEY,
level_number INT NOT NULL CHECK (level_number >= 0))
AS
BEGIN
IF @.root_node IS NULL
SELECT @.root_node = node_name
FROM Tree
WHERE parent_id IS NULL
IF NOT EXISTS (SELECT * FROM Tree WHERE node_name = @.root_node)
RETURN
DECLARE @.level_number INT,
@.next_level_number INT
SELECT @.level_number = 0,
@.next_level_number = 1
INSERT INTO @.nodes (node_id, level_number)
SELECT node_id, @.level_number
FROM Tree
WHERE node_name = @.root_node
WHILE EXISTS (SELECT * FROM @.nodes
WHERE level_number = @.level_number)
BEGIN
INSERT INTO @.nodes (node_id, level_number)
SELECT T.node_id, @.next_level_number
FROM @.nodes AS N
INNER JOIN
Tree AS T
ON N.level_number = @.level_number AND
N.node_id = T.parent_id
SELECT @.level_number = @.next_level_number,
@.next_level_number = @.next_level_number + 1
END
RETURN
END

CREATE VIEW TreeDepth (depth)
AS
SELECT COALESCE(MAX(level_number) + 1, 0)
FROM Descendants(DEFAULT)

SELECT depth
FROM TreeDepth

depth
4

To find the depth of the tree rooted at other than the tree's root node,
e.g., the depth of the tree rooted at node C, one can do the following

SELECT COALESCE(MAX(level_number) + 1, 0) AS depth
FROM Descendants('C')

depth
2

Regards,
jag