how to find every node's level in a tree structure using SQL query

[henryz]

Hello everyone,

I have a table like this:

CREATE TABLE Items
(
childCode VARCHAR(25) NOT NULL UNIQUE,
parentCode VARCHAR(25) NULL,
itemName VARCHAR(25) NOT NULL,
ID INT NOT NULL PRIMARY KEY,
)

Items
===============================
parentCode childCode itemName ID
AA BB apple 1
AA HH onion 2
BB CC orange 3
BB DD banana 4
CC EE grape 5
DD FF apricot 6
EE GG carrot 7


The structure looks like this:

AA
/ BB HH
/ CC DD
/ EE FF
/
GG

If I say that the root node "AA" is at level 1, is it possible to find out
all the nodes' levels by using SQL query, and the query works fine on SQL
server 7.0?

Thanks a lot in advance


henry