[thelist] Nested Queries

[rudy]

Josh, you didn't say what database you're using, but if it's oracle, look
up CONNECT BY

other than proprietary extensions like oracle's, sql does not offer much to
help you traverse the traditional tree structure efficiently if there is an
indeterminate number of levels

many people resort to executing queries inside a loop in the calling
program, and while that works, it's hell on performance efficiency

if the depth of levels can be constrained, i.e. subtrees cannot be nested
more than N deep (you would control this in the app that adds subtrees),
then the sql does become quite manageable and the performance is actually
excellent -- it uses a left outer join of the table to itself N-1 times

if you cannot constrain N, then you may want to consider an alternate
method for storing the data, nested sets

here, i'll let joe celko explain it --

  http://www.dbmsmag.com/9603d06.html
  http://www.dbmsmag.com/9604d06.html
  http://www.dbmsmag.com/9605d06.html
  http://www.dbmsmag.com/9606d06.html
  http://www.intelligententerprise.com/001020/celko1_1.shtml


rudy

p.s. David, you may want to consider having the child point to the parent
rather than the other way around