retrieving tree sub nodes - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

retrieving tree sub nodes

Author

Topic

martian
Starting Member

3 Posts

Posted - 2007-04-16 : 19:23:13

I have a tree structured table like the ff.

which looks like the one below in tree view.

I'd like to get the rows under a specified elementnumber which may have unlimited sub-nodes.

i.e. If i want only 'Level1' i would get results from row 2-5.

any help is greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-16 : 19:29:16

Your images aren't working.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

martian
Starting Member

3 Posts

Posted - 2007-04-16 : 20:07:41

Sorry about that...

0.000000000000| |Root|000051_191|
1.000000000000|000051_191|Level1|000052_191|
1.000000000000|000052_191|act1.1|000053_191|
2.000000000000|000052_191|Level1.1|000054_191|
1.000000000000|000054_191|act1.1.1|000055_191|
2.000000000000|000051_191|Level2|000056_191|
1.000000000000|000056_191|act2.1|000057_191|
2.000000000000|000056_191|act2.2|000058_191|
3.000000000000|000051_191|Level3|000059_191|

here's a the tree view

Root
-Level1
--act1.1
--Level1.1
---act1.1.1
-Level2
--act2.1
--act2.2
-Level3

martian
Starting Member

3 Posts

Posted - 2007-04-16 : 20:35:35

sorry again... i forgot to include the column names

SiblingNumber|ParentElementNumber|Name|ElementNumber
0.000000000000| |Root|000051_191|
1.000000000000|000051_191|Level1|000052_191|
1.000000000000|000052_191|act1.1|000053_191|
2.000000000000|000052_191|Level1.1|000054_191|
1.000000000000|000054_191|act1.1.1|000055_191|
2.000000000000|000051_191|Level2|000056_191|
1.000000000000|000056_191|act2.1|000057_191|
2.000000000000|000056_191|act2.2|000058_191|
3.000000000000|000051_191|Level3|000059_191|

here's a the tree view

Root
-Level1
--act1.1
--Level1.1
---act1.1.1
-Level2
--act2.1
--act2.2
-Level3

I'd like to get the rows under a specified elementnumber which may have unlimited sub-nodes.

i.e. If i want only 'Level1' i would get results from row 2-5.

any help is greatly appreciated.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-17 : 01:04:42

are you using 2005? if so you might have a look at CTEs:

http://msdn2.microsoft.com/en-us/library/ms190766.aspx
http://msdn2.microsoft.com/en-us/library/ms175972.aspx

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources