find level for every node in tree structure 1

[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

 

[tlbroadbent]

There is no simple SQL solution. Here are some links with various SQL solutions to this age-old problem.

http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/recursion.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_5yk3.asp

http://www.sqlteam.com/ItemPrint.asp?ItemID=1353

http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci537290,00.html

http://www.intelligententerprise.com/001020/celko.shtml

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[henryz]

Hello Terry,

Thank you so much for your response. Before I implement Joe Celko's ideas into my case, I test some of his sample
codes using
SQL server 7.0. I create a Personnel table (table 2) in a temp database,
then enter data into the table. Using SQL server Query Analyzer to do the
query, I have exceptions all the time.

1). While I test the following code:

"SELECT COUNT(P2.emp) AS indentation, P1.emp
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY P1.emp
ORDER BY P1.lft;"

I get an exception saying "Server: Msg 8127, Level 16, State 1, Line 1
Column name 'P1.lft' is invalid in the ORDER BY clause because it is not
contained in either an aggregate function or the GROUP BY clause."

2). I test another piece of code
"SELECT P2.*
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp = :myemployee;"
I received message saying "Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ':'."

3). try to find the level of each node, I try the code
"DECLARE Out_Tree CURSOR FOR
SELECT P1.lft, COUNT(P2.emp) AS indentation, P1.emp
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY P1.emp
ORDER BY P1.lft;"

but I get "Server: Msg 8120, Level 16, State 1, Line 2
Column 'P1.lft' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause."

I don't know so far, are there anything wrong with my SQL server or these
codes only work OK in Oracle DB. Suggestions are most appreciated.

Many thanks

Henry

 

[tlbroadbent]

I certainly didn't expect you to copy Mr. Celko's code line for line. He writes code for ANSI SQL. You have to adjust for T-SQL in SQL Server. You can make changes to the code to correct any of the problems.

1) SELECT COUNT(P2.emp) AS indentation, P1.emp
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY P1.emp
ORDER BY P1.emp

2) SELECT P2.*
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp = <value_you_choose>

3) DECLARE Out_Tree CURSOR FOR
SELECT P1.lft, COUNT(P2.emp) AS indentation, P1.emp
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY p1.lft, P1.emp
ORDER BY P1.lft Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[henryz]

Hello Terry,

Thanks you so much for your response. I decide to use a
stored procedure to calculate the level of every node. My idea is to insert
all the data from the existing table to the virtual table, and calculate the
level, but I have not luck yet

The code is in the following:

CREATE PROCEDURE usp_GetItemLevel

AS
CREATE TABLE #Tree
(
childCode VARCHAR(25) NOT NULL UNIQUE,
parentCode VARCHAR(25) NULL,
itemName VARCHAR(25) NOT NULL,
ID INT NOT NULL PRIMARY KEY,
lvl int NOT NULL
)

DECLARE @lvl AS int
SET @lvl = 0

INSERT INTO #Tree
SELECT childCode, parentCode, itemName, ID, @lvl
FROM Items

WHILE @@ROWCOUNT > 0
BEGIN
SET @lvl = @lvl + 1

INSERT INTO #Tree
SELECT I.childCode, I.parentCode, I.itemName, I.ID, @lvl
FROM Items AS I JOIN #Tree AS T
ON I.childCode = T.parentCode AND T.lvl = @lvl - 1
END

SELECT *
FROM #Tree
ORDER BY lvl desc


After execute the code , the level for every node is 0 which is not what I
want. Could you please have a look for my code and give me some suggestions.

Thanks a lot for your great help

Henry

 

[EdVassie]

Maybe one day recursive SQL will get implemented in SQL Server, as it has existed in its main competitors for some years... |-0 This posting is a personal opinion only, and may not reflect reality.