Yes, that can be the annoying thing about traversing such relationships. You might check out a book by Joe Celko entitled SQL for Smarties. In that book, as I recall, he has some suggestions for how to improve queries against tables like this by creating a sort of "helper" table that establishes certain information about the relationships in the table, such as the number of levels between any given parent element and any one of its children. Using such a table, you can determine with a single query, how many descendant elements a particular element has (not just immediate descendants, but all descendants), and you can determine what the descendant elements are, so that you can obtain them all with one query, thereby avoiding repeated queries against the table.

Incidentally, I just wanted to clarify, as I have access to my copy of SQL for Smarties now. The "helper" table I was talking about has to do with the "Transitive Closure Model," and it's in Chapter 28 of the book. It's a great book, and I highly recommend it as it has solutions to a lot of complex problems that pop up in database programming.

Matthew Burr

Edited by - mdburr on 10/30/2002 11:08:35 AM