Recursive tree - get a specific tree

Last Post 04 Sep 2006 08:42 AM by hdsit. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
hdsit
New Member
New Member

--
04 Sep 2006 05:18 AM
i have a table with numerous trees:

ID parentid name
0 0 Parent
2 0 tree1
3 0 tree2
4 0 tree3
5 2 sub_tree1
6 3 sub_tree2
7 4 sub_tree3
8 5 sub_sub_tree1
9 6 sub_sub_tree2

I use (have found an example) following stored procedure to get one of these trees:

Create PROC [dbo].[ShowHierarchytest]
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ID int, @category varchar(30), @parentID int
SET @category = (SELECT category FROM dbo.tbl_testTree WHERE ID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 4) + @category
SET @ID = (SELECT MIN(ID) FROM dbo.tbl_testTree WHERE parentID = @Root)
WHILE @ID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchyTest @ID
SET @ID = (SELECT MIN(ID) FROM dbo.tbl_testTree WHERE parentID = @Root AND ID > @ID)
END
END

I would like to be able to get one specific tree, and all nodes under this tree - ex tree2 by passing in the parameter 3.

The result should be:
ID parentid name
3 0 tree2
6 6 sub_tree2
9 6 sub_subtree2

how do i get the result back (instead of print), so that i can use it in a grid in a windows form.

I'm still a newbee on this...
SQLUSA
New Member
New Member

--
04 Sep 2006 06:50 AM
You are on the right track, but it is very difficult to do it in SQL 2000.

Good news: HEEEEEEEEEEEEEERE comes SQL 2005 with TREE PROCESSING built in.

Check out this article: http://www.sqlusa.com/bestpractices2005/executiveorgchart/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices...eorgchart/

The CTE with the UNION ALL does the TREE PROCESSING.


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
hdsit
New Member
New Member

--
04 Sep 2006 08:42 AM
quote:

Originally posted by: SQLUSA
You are on the right track, but it is very difficult to do it in SQL 2000.

Good news: HEEEEEEEEEEEEEERE comes SQL 2005 with TREE PROCESSING built in.

Check out this article: http://www.sqlusa.com/bestpractices2005/executiveorgchart/" target="_blank" rel="nofollow">http://www.sqlusa.com/bestpractices...eorgchart/

The CTE with the UNION ALL does the TREE PROCESSING.


Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World


Well actually i'm running 2005 - Can you give a little more help...
I can get it run correct on adventureworks, but not on my table:

USE test;

WITH
Executive(ID)
AS
( SELECT distinct parentID
FROM tbl_tree
WHERE parentID is not null),

OrgChart(parentID, ID, EmployeeLevel)
AS
(
SELECT parentID, ID, 0 AS EmployeeLevel
FROM tbl_tree
WHERE parentID IS NULL
UNION ALL
SELECT e.parentID, e.ID, EmployeeLevel + 1
FROM tbl_tree e
INNER JOIN OrgChart d
ON e.parentID = d.ID
)

SELECT replicate('^', (EmployeeLevel)* 10) + Name+' '+id as Executive
FROM Executive m
JOIN OrgChart oc
ON m.ID = oc.ID

JOIN tbl_tree e
ON oc.ID = e.ID
JOIN Person.Contact c <<<-what table shall i join here
ON c.ContactID=e.ContactID
WHERE EmployeeLevel < 2
ORDER BY EmployeeLevel, Executive;
GO

hdsit
New Member
New Member

--
05 Sep 2006 12:43 AM
Like this:

CREATE PROCEDURE [dbo].[GetTree]
@paramID int
as
begin
WITH DirectReports(parentid, ID, name, Level) AS
(
SELECT parentid, ID, name, 0 AS Level
FROM dbo.tbl_Tree
WHERE id = @paramID
UNION ALL
SELECT e.parentid, e.ID, e.name, Level + 1
FROM dbo.tbl_Tree e
INNER JOIN DirectReports d
ON e.parentid = d.ID
)
SELECT parentid, ID,name, Level
FROM DirectReports ;

END
Seems to give me what i needed...
SQLUSA
New Member
New Member

--
05 Sep 2006 04:13 PM
Yes that is the idea. You can do all different kind of neat TREE tricks.

Remember that single and DOUBLE parents trees supported! A revolutionary advantage over ORACLE.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
You are not authorized to post a reply.

Acceptable Use Policy