Any ideas how to traverse a simple tree in SQL? 1

[fousek]

Hello all!

I have a table defined as:

create table OrgTree (parent int, child int)

OrgTree has the following data:

1,2
1,3
2,4
2,5
3,6
4,7
4,8
8,9

Is there a SQL stmt that will return, given a particlar parent, all of the children (and children's children, etc.) of that parent?

So, parent value 1 returns 2,3,4,5,6,7,8,9
parent value 2 returns 4,5,7,8,9
parent value 3 returns 6
etc.

Thanks in advance for your help!!

Palmer F

 

[MalcolmW]

There are several chapters on this subject in SQL For Smarties by Joe Celko. Seems to be a good example of "square peg, round hole" Malcolm
wynden@telus.net

 

[MikeLacey]

fousek,

If you're using Oracle there is an Oracle extension specially for dealing with trees. What DB are you using?
Mike
michael.j.lacey@ntlworld.com

 

[fousek]

Mike!

This is MS SQL Server.

Actually I have coded a pretty hideous looking piece of dynamic code that uses self-joins and unions that works, but only if I know the numbers of levels my tree has.

Thanks for your input, though!

Palmer

 

[MikeLacey]

Palmer,

That's what you get for using Microsoft products.... tut tut...

Seriously though; SQL Server is hardly a trivial product. I find it hard to believe it does not support tree style data in a reasonable fashion.....
Mike
michael.j.lacey@ntlworld.com

 

[bobby1977]

Well, in Oracle u can retrieve for example all children for parent given by "1" value using:

select parent, level from OrgTree where parent <> 1 start with parent = 1 connect by prior child = parent;

each field returned from this query has an associated pseudocolumn called LEVEL which tells u the depth of given node into the tree. I do not know if MS SQL implements hierarchical queries but see if there are some tips on the idea given above.

I hove u can use some of my response...

 

[DataArch]

use a recursive stored procedure... it is not that bad; just don't try it on a millions rows unless you have a snickers.

 

[tdatgod]

Hi,
This is the ANSI FORUM. Is there a way in ANSI SQL to do this recursion?

This is your basic Parts explosion query.

Part 1 contains part 2,
Part 2 contains part 3 and 4
Parts 3 and 4 contains some parts.

--