tree structure select problem

[ven]

hello i have a table with four fields : name_id, name, tree_id, level with
these data:

1 name1 001 1
2 name2 002 1
3 name3 003 1
4 name4 001001 2
5 name5 001002 2
6 name6 001003 2
7 name7 001001001 3

tree:

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

and procedure which add nodes to tree looks tree.add(idparent,idchild,name)

how to write select which returns idparent,idchild and name then i can add
nodes in loop reading datareader ?

[ven]

sorrry i forgot one think the select should return somethin like :

root 1 name1
root 2 name2
root 3 name3
4 1 name4
5 1 name5
6 1 name6
7 4 name7
U¿ytkownik "ven" <ve*****@poczta.onet.pl> napisa³ w wiadomo¶ci
news:dk**********@news.onet.pl...

hello i have a table with four fields : name_id, name, tree_id, level with
these data:

1 name1 001 1
2 name2 002 1
3 name3 003 1
4 name4 001001 2
5 name5 001002 2
6 name6 001003 2
7 name7 001001001 3

tree:

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

and procedure which add nodes to tree looks
tree.add(idparent,idchild,name)

how to write select which returns idparent,idchild and name then i can add
nodes in loop reading datareader ?

[Erland Sommarskog]

ven (ve*****@poczta.onet.pl) writes:

hello i have a table with four fields : name_id, name, tree_id, level with
these data:

1 name1 001 1
2 name2 002 1
3 name3 003 1
4 name4 001001 2
5 name5 001002 2
6 name6 001003 2
7 name7 001001001 3

tree:

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

and procedure which add nodes to tree looks
tree.add(idparent,idchild,name)

how to write select which returns idparent,idchild and name

Since I answered a similar query earlier this week, I'm lazy and refer
you to that thread:
http://groups.google.com/group/comp....9fcc60b9ca2ed1

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

You might want to look at the Nested Sets model for trees instead.

[Tony Rogerson]

The nested set method you will see is:-

1) Difficult to maintain
2) Does not scale nor perform well out of the lab and in the real
world.

A better method is that developed by Itzik Ben Gan, information here:
http://www.windowsitpro.com/Articles...layTab=Article

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ven" <ve*****@poczta.onet.pl> wrote in message
news:dk**********@news.onet.pl...

hello i have a table with four fields : name_id, name, tree_id, level with
these data:

1 name1 001 1
2 name2 002 1
3 name3 003 1
4 name4 001001 2
5 name5 001002 2
6 name6 001003 2
7 name7 001001001 3

tree:

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

and procedure which add nodes to tree looks
tree.add(idparent,idchild,name)

how to write select which returns idparent,idchild and name then i can add
nodes in loop reading datareader ?