|
As presented in "Joe Celko's SQL for smarties" and
discussed in many newsgroups, the nested set solutions
are up to 1000 times faster than their equivalent methods (mainly based on
cursors or recordsets).
While the standard discussions turn around maintenance
(adding and deletion of nodes
in the "graph"), there was no example about the BOM
problem based on that kind of solutions, at least, up to now, since now, you
can find one, in the Jet-Access 2000 zipped database included here. That
database has one table, the nested set (we assume you are familar with the
notion), a single form showing graphically the tree represented in the
table, and one query returning the list of the required
elements, and in which quantity, to make an arbitrary item described in the
nested set.
As you can see by yourself, there is no VBA, no recursion,
only plain SQL is used to solve that kind of problem. The query can surely be
re-used for any nested-set, not just for the one given as example.
|
