SQL Trees & ...I must be missing something

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Trees & ...I must be missing something

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-10 : 10:45:32

I guess I missing something, I decided to use Northwind and follow Rob's example to build the tree table. I cut and past the DDL, then do an INSERT INTO From the employees table (there =was no sample here, but I though it was a simple Insert), then try to apply the Parent Nodes (Not sure I understand the difference between a Node and a Parent Id). But the Results I get seem to be incorrect. Any one else have this problem.

Below is a sample. Any comments greatly appreciated.

Brett

8-)

PS My goal is to write part two of this article..but that seems a long way off if I can't even populate the tree.

Code:

Use NorthWind
Go

SELECT * FROM Employees
Go

CREATE TABLE Tree (
Node int NOT NULL IDENTITY(100, 1),
ParentNode int,
EmployeeID int NOT NULL,
Depth tinyint,
Lineage varchar(255) )
Go

INSERT INTO Tree (EmployeeId) SELECT EmployeeId From Employees
Go

SELECT * FROM Tree
Go

UPDATE T SET T.ParentNode=P.Node
FROM Tree T
INNER JOIN Employees E ON T.EmployeeID=E.EmployeeID
INNER JOIN Employees B ON E.ReportsTo=B.ReportsTo
INNER JOIN Tree P ON B.EmployeeID=P.EmployeeID
Go

SELECT t.*, e.ReportsTo FROM Tree t, Employees e Where T.EmployeeId = E.EmployeeId
Go

Drop Tree
Go

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-10 : 10:51:03

And to elaborate, the reporting structure should look like:

-2
--1
--3
--4
--8
--5
---6
---7
---9

The results from the update are (the last column is reports to from Employees):

100-----103-----3-------NULL----NULL----2
101-----103-----4-------NULL----NULL----2
102-----103-----8-------NULL----NULL----2
103-----103-----1-------NULL----NULL----2
104-----NULL----2-------NULL----NULL----NULL
105-----105-----6-------NULL----NULL----5
106-----105-----7-------NULL----NULL----5
107-----103-----5-------NULL----NULL----2
108-----105-----9-------NULL----NULL----5

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-10 : 10:53:13

NEVER MIND:

The update was Wrong:

Should have been:

UPDATE T SET T.ParentNode=P.Node
FROM Tree T
INNER JOIN Employees E ON T.EmployeeID=E.EmployeeID
INNER JOIN Employees B ON E.ReportsTo=B.EmployeeID
INNER JOIN Tree P ON B.EmployeeID=P.EmployeeID
Go

SORRY

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources