May 15, 2007 at 3:10 pm
I have a table with two columns, parentID and childID. What I want to do is the following:
Given a parentID, get all childID's and the childrens childID's
Does anyone have a way to do it in T-SQL?
Thanks,
Michael
May 15, 2007 at 3:15 pm
Select T1.ChildID Level1, T2.Childid AS Level2 FROM dbo.Table T1 LEFT OUTER JOIN dbo.Table T2 ON T1.ChildID = T2.ParentID where Parent = @id
Not tested but this should give you the idea. If you want ALL levels of childs then this is another process altogether.
May 15, 2007 at 3:22 pm
As RGR stated Getting Levels is altogether another Monster
Google "Trees in SQL" and you will get a Miriad of Articles and information discussing this.
For Example.
http://www.dbazine.com/oracle/or-articles/tropashko4
Its not just a simple query we can give you, it all depends on what your trying to do.
May 15, 2007 at 4:42 pm
Thanks everyone. Here is what I really want
A is the parent of B, C, D
B is the parent of E
C is the parent of F, G, H
G is the parent of Z
If I give C, I want it to return F, G, H, and Z
make sense?
May 16, 2007 at 9:01 am
I have been doing hierarchical queries in RDBMS (Oracle, SQL Server 2000/2005, DB2) for 20+ years.
For SQL Server, there are a number of ways to do this in T-SQL. However, the solution can vary depending upon your application and the use patterns.
Generally, the best way that I have done this is to write a UDF that returns a table result rather than in-line SQL code.
Solutions are:
1. A block of code that navigates down the tree using "push" and "pop" logic. See Microsoft KB article 248915.
Benefits:
1. Full control over what you're doing
2. Ordering of the intermediate values, if necessary.
2. If you're using SQL Server 2005, you can use the recursive CTE (Common Table Expression) logic. There are numerous examples of this.
Drawbacks:
1. Unfortunately, the SQL Server 2005 recursive Common Table Expression (CTE) does not return the rows in hierarchical tree order. Contrast with Oracle's CONNECT BY syntax. In addition, there is no way to order (sort) the intermediate nodes like Oracle's CONNECT BY ... ORDER SIBLINGS BY syntax.
Therefore, a "sort field" (column) must be constructed by the CTE portion of the query which can be used later.
2. Performace. My benchmarking testing (SQL Server 2005 SP1) using a CTE vs. a "tree walk" function showed that the "tree walk" function was much faster than the CTE. Your mileage (performance) may vary.
May 16, 2007 at 10:54 pm
Here's an example... you can't do unlimited hierarchies, but it's normally good enough. And, it's not your specific example... it for something I did for someone else... thought it might provide a decent example, though...
--===== If the temporary demonstration tables exist, drop them
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
IF OBJECT_ID('TempDB..#Hierarchy') IS NOT NULL
DROP TABLE #Hierarchy
--===== Create the table to hold the data posted for test
CREATE TABLE #MyHead
(
QuestTreeUID INT PRIMARY KEY CLUSTERED,
ShortDesc VARCHAR(20),
LongDesc VARCHAR(20),
ParentUID INT,
QuestNodeUID INT
)
--===== Populate the table with the test data posted
INSERT INTO #MyHead(QuestTreeUID,ShortDesc,LongDesc,ParentUID,QuestNodeUID)
SELECT 14,'Smartwaste','Smartwaste',0,1 UNION ALL
SELECT 15,'Skip','Skip',14,3 UNION ALL
SELECT 991,'Product','Product',14,2 UNION ALL
SELECT 17,'Material','Material',991,3 UNION ALL
SELECT 18,'Product','Product',991,3 UNION ALL
SELECT 19,'Feedback','Feedback',14,2 UNION ALL
SELECT 20,'Origin of waste','Origin of waste',19,3 UNION ALL
SELECT 21,'Cause','Cause',19,3
--===== Test setup complete, we're ready to rock!
--===== Create and preset a level counter.
DECLARE @CurrentLevel INT
SET @CurrentLevel = 0
--===== Create the Hierarchy table
CREATE TABLE #Hierarchy
(
QuestTreeUID INT PRIMARY KEY,
ParentUID INT,
Level INT,
Hierarchy VARCHAR(8000),
QuestNodeUID INT
)
--===== Seed the Hierarchy table with the top level
INSERT INTO #Hierarchy
(QuestTreeUID,ParentUID,Level,Hierarchy,QuestNodeUID)
SELECT QuestTreeUID,
ParentUID,
0 AS Level,
STR(QuestTreeUID,7)+' ' AS Hierarchy,
QuestNodeUID
FROM #MyHead
WHERE ParentUID =0
--===== Determine the rest of the hierarchy
WHILE @@ROWCOUNT > 0
BEGIN
SET @CurrentLevel = @CurrentLevel + 1 --Started at 0
INSERT INTO #Hierarchy
(QuestTreeUID,ParentUID,Level,Hierarchy,QuestNodeUID)
SELECT p.QuestTreeUID,
p.ParentUID,
@CurrentLevel AS Level,
h.Hierarchy + STR(p.QuestTreeUID,7)+' ' AS Hierarchy,
p.QuestNodeUID
FROM #MyHead p
INNER JOIN #Hierarchy h
ON p.ParentUID = h.QuestTreeUID
AND h.Level = @CurrentLevel - 1
END
--===== Produce the hierarchical report
SELECT p.QuestTreeUID,p.ParentUID,REPLICATE('-----',h.Level)+SPACE(SIGN(h.Level))+ p.ShortDesc
FROM #MyHead p,
#Hierarchy h
WHERE NOT (h.Level = 1 AND h.QuestNodeUID = 3) --Skips out of line entries
AND p.QuestTreeUID = h.QuestTreeUID
ORDER BY h.Hierarchy
select * from #hierarchy
--Jeff Moden
Change is inevitable... Change for the better is not.
May 17, 2007 at 12:56 am
Joe Celko's nested set model is worth a look too.
http://www.intelligententerprise.com/001020/celko.jhtml
---------------------------------------
elsasoft.org
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply