Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1700
  • Last Modified:

Tree Structure

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
jimmy282
Asked:
jimmy282
1 Solution
 
prokniCommented:
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
 
jimmy282Author Commented:
Using Recursive Queries I have already done...
I want it in one query for performance gains.


ANy Ideas...
0
 
DrSQLCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kelfinkCommented:
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
 
johan_brohnCommented:
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
 
nico5038Commented:

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
 
NetminderCommented:
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now