SQL Tree Navigation

[cheald]

Hi --

I've got two tables, defined as such

TREE:
id INT | parent_id INT

LEAVES:
id INT | parent_id INT | flag INT

Given a leaf, I want to be able to find the nearest leaf containing a certain value of flag. Now, I can do this by iterating children, then parents, and recursively walking the tree. However, I was wondering if anyone knew of a way to find a less query-intensive solution, as it's possible to make a very large number of queries with the method, and I want to keep the queries to a minimum. Any ideas?

Basically, what's the fastest way to find another leaf with a certain value that is closest to the origin leaf?

Thanks

Chris

[Anon]

I don't think there would be a general solution without walking the tree. But you might be able to tweak it some by searching leaves close to the origin in three or four queries (all siblings, children, cousins, etc). Depending on how wide your tree is you would probably cover a lot of leaves before resorting to the recursive search of the entire tree.