How to traverse a tree in SQL? Sybase edition Possible Duplicate:
  Traversing tree-like data in a relational database using SQL  




Whats the best way to traverse a tree in sybase?

Basically, what I want is to gather all the decedents of a certain node.
(aka the decedents of the root node is every node in the tree)

I've thought of two things I can do.


Use recursion on the java end and query multiple times. (obviously less than optimal)
Transform the naturally recursive problem into an iterative problem and use a while loop and one or two temp tables. This wouldn't actually be that bad. 


Is there a better way?
It's bugging me that this question doesn't have at least one answer.  So...

You could use a mix of primary/foreign keys within the table per Joe Celko.  Alternatively, you might want to consider a closure table.

Generally, the table referencing itself seems to be the most common method for handling this.  Of course, you would have to consider looping.  Some DBMSs have CTEs which can use Recursion, so the looping is inherent in the CTE.  For Sybase, you'll probably have to throw a loop in code somewhere.