Get the names for different levels
-
-
If you are using SQL 2005 you want to have a look at Common Table Expressions (CTE's) otherwise have a look for recursive queries there are lots of questions/articles about it on this site.
James
--
James Moore
Red Gate Software Ltd -
There is no generic solution for an arbitrary number of levels. But if you know that there is a reasonable maximum number of levels then it can be handled with a series of unions. Also, I presume you only want to show the path from the root node to each terminal node. This covers 5 levels.
select t1.id,t2.id,t3.id,t4.id,t5.id
from tree t1,tree t2,tree t3,tree t4,tree t5
where t1.id=1
and t1.id=t2.parentid
and t2.id=t3.parentid
and t3.id=t4.parentid
and t4.id=t5.parentid
and t5.id not in (select isnull(parentid,0) from tree)
union
select t1.id,t2.id,t3.id,t4.id,null
from tree t1,tree t2,tree t3,tree t4
where t1.id=1
and t1.id=t2.parentid
and t2.id=t3.parentid
and t3.id=t4.parentid
and t4.id not in (select isnull(parentid,0) from tree)
union
select t1.id,t2.id,t3.id,null,null
from tree t1,tree t2,tree t3
where t1.id=1
and t1.id=t2.parentid
and t2.id=t3.parentid
and t3.id not in (select isnull(parentid,0) from tree)
union
select t1.id,t2.id,null,null,null
from tree t1,tree t2
where t1.id=1
and t1.id=t2.parentid
and t2.id not in (select isnull(parentid,0) from tree)
By the way, there is quite a bit of literature on how to handle hierarchies using sql.
-
Search hierarchies here or on Google for more samples.
-
Uh-huh... What is that that you would like to do for the following?
ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4
6 Branch 2 3
7 Leaf 6 4
8 Leaf 6 4
9 Leaf 6 4
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think what he wants is
Root Trunk Branch Leaf
Root Trunk Branch Stem
Root Trunk Branch Leaf
Root Trunk Branch Leaf
Root Trunk Branch Leaf
The query I showed does essentially that. If you display the node names instead of node ids you may need to use 'union all' because of duplicate node names.
The query actually was adapted from another problem I dealt with a while ago which asked for all nodes on or below a given node.
-
No problem, Michael. But I am curious what the OP actually wants... it looks like he's trying to display the tree for a current drill down into a set of screens. I'm thinking that he doesn't actually want the downline for a given root... I'm thinking that he want's the upline for a given leaf...
What say thee, Sree?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply