Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Tree Structure

Posted on 2002-05-23
7
1,655 Views
Last Modified: 2008-03-06
Hi,

I have a table in SQL2000 called modules. It has a bunch of fields...about 15
I am concerned with ID,ParentID,Name

There can be any level of depth in the Tree.
I want to get the complete tree structure in one query.

e.g.
Id Parentid Name
1    0       ID1
2    0        ID2
3    1       ID3
4    2       ID4
5    2        ID5
6    1        ID6
7    6        ID7
8    7        ID8
9    9        ID8

and so on...

So I want it to get it like this from the query or SP whatever
Id
1  
  3
  6
    7
     8
           9
2  
  4
  5
     
And so on with all other columns also included in the resultset.

I dont have any Problems using a Query or a StoredProc.

Can anyone help?


0
Comment
Question by:jimmy282
7 Comments
 
LVL 2

Expert Comment

by:prokni
ID: 7030085
In one query. Are u sure? I would do it with recursive functions out side of Sql. What language are you more confortable with?
0
 
LVL 11

Author Comment

by:jimmy282
ID: 7030087
Using Recursive Queries I have already done...
I want it in one query for performance gains.


ANy Ideas...
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 7030397
jimmy282,
   If you were using Oracle, you'd use the "connect by" to create a tree structure.  What Microsoft recommends (since SQL 2k doesn't HAVE that, is a stored procedure.  The difficulty is - what does that proc DO?

   I'd suggest that ordering/sorting is the first order of business.

   Ideally, I'd like my function to return a value that I can use to sort my tree.  I'd suggest we do that by walking back up the tree using the parentid = id.  Thus, our select would be:

select tree_key(id),Name
  from mytable
order by tree_key(id);

tree_key would be a function that returns a string.  That string would consist of a concatenation of the parentid and the id with every id of every record "above" it in the virtual tree stuck ont the front.  You'd want to use a formatted number string.  I can only write this one quickly in Oracle PL/SQL, but that should give you an idea:

create or replace function
tree_key(startid number) return varchar2 is

treehold varchar2(2000);
treepoint number;

begin
   treehold := to_char(startid,'0999');
   treepoint := startid;
   loop
      select nvl(parentid,0) into treepoint
       from mytable where id=treepoint;
      treehold := to_char(treepoint,'0999') || treehold;
   end loop;
end;
exception when others then return(treehold);


So, you'd get:

00000001             ID1
000000010003         ID3
000000010006         ID6
0000000100060007     ID7
00000001000600070008 ID8
00000002             ID2
000000020004         ID4
000000020005         ID5

But, the last one:
9    9        ID8

can't be reached by the tree.

Good luck!
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 5

Expert Comment

by:kelfink
ID: 7042977
Try using the Nested Hierarchy Model, discussed by Joe Celko.
He details the scheme in SQL for Smarties
ISBN 1-55860-576-2.

To get a brief description of it, refer to
http://groups.google.com/groups?q=celko+nested+set+atomic&hl=en&rnum=1&selm=Oy21PVU6AHA.1380%40tkmsftngp05

Read the whole article, as Celko is answering someone's question with a pretty-complete description to the model.

I've used this model to represent unlimited hierarchies using generic SQL, and using no recursive functions.

The same SQL works on MS SQL, Oracle, mySQL, Interbase, DB2, postgres, etc..

With the structure, you can query the whole tree to display the hierarchical structure using one query.

You can insert, delete, summarize the depth, show a lengthy parent-child chain, etc, with simple statements.

0
 
LVL 1

Expert Comment

by:johan_brohn
ID: 7046841
If Table Tree looks like this:
Id Parentid Name
1    0       ID1
2    0        ID2
3    1       ID3
4    2       ID4
5    2        ID5
6    1        ID6
7    6        ID7
8    7        ID8
9    8        ID9


DECLARE @Level Int,@Rows Int

SET @Level = 0

--Temporary table
CREATE TABLE #Test2(PK Int IDENTITY (1,1),Id Int, level Int, Name varchar(30),SortColumn varchar(1000))

INSERT INTO #Test2 (Id, Level, Name, SortColumn)
SELECT Id, 0, Name, '0'
FROM dbo.Tree WHERE ParentId = 0
SELECT @Rows = @@RowCount

WHILE @Rows > 0
BEGIN
  INSERT INTO #Test2 (Id, Level, Name, SortColumn)
  SELECT S.Id, @Level + 1, S.Name, SortColumn + STR(T.PK)
  FROM #Test2 T
  JOIN Tree  S On S.ParentId=T.Id And T.Level=@Level

  SELECT @Rows = @@RowCount, @Level = @Level + 1
END

SELECT * FROM #Test2
ORDER BY SortColumn, Name

DROP TABLE #Test2
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7265815

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7296593
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question