Solved

Tree Structure

Posted on 2002-05-23
7
1,623 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now