2

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.

  1. Use recursion on the java end and query multiple times. (obviously less than optimal)
  2. 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?

|improve this question
  • I don't see any of the solutions mentioned in the other question viable for sybase. please correct me if I'm wrong. – user606723 Aug 4 '11 at 19:37
  • That's why you have one of the main sources of reading about SQL trees and hierarchies - Joe Celko. Few samples: Trees in SQL, Trees and Hierarchies in Oracle, Nested set model. – Marian Aug 4 '11 at 22:07
  • 1
    Both Celko's book and closure tables (mentioned in my response to the original question) are usable in Sybase; for your specific problem, you might also consider using path enumeration (I don't have a handy reference to that one, but a bit of Googling should pay off). – TML Aug 11 '11 at 19:58
3

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.

|improve this answer
  • Some of us didn't answer because we considered this question being some kind of a duplicate of a similar question. For the cases where we don't have helpful proprietary features of the SQL engine (like CTE, HIERARCHY, CONNECT BY) to do the hierarchical stuff, than we have the theory that was already mentioned. – Marian Aug 12 '11 at 23:26
  • I agree. Let's close this as duplicate. – Richard Aug 13 '11 at 2:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.