?
Solved

Tree Structure

Posted on 2002-05-23
7
Medium Priority
?
1,686 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
[X]
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
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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 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