EXPERT RESPONSE
In general, this requires recursion. Yours is the classic hierarchical
structure, implemented using the adjacency model.
As I mentioned in a previous answer,
Recursion in SQL, only Oracle, to my knowledge,
provides a way to do recursion.
Of course, if you can store your data using the
nested set model, then the solution is
straight-forward, I'm told (I don't actually use the nested set model
myself). See
Converting an adjacency list model to a nested set model.
If you must stick with the adjacency model, you can
"work around" the need for recursion,
provided you are able to set a realistic limit to the depth of the subtree.
Just use an appropriate number of self-joins. If the path from
the CEO to the lowliest cube jockey is eight layers of management,
then have nine self-joins. Most databases are pretty good at joining tables,
handling even a dozen or two with ease.
|
