Queries: BOM, with Joe Celko Nested Sets Here is the reference to Joe Celko's SQL material.
Randy Peterson
---Posted by Michel Walsh---
--------------------------------------------------------
Queries: BOM, with Joe Celko Nested
Sets
--------------------------------------------------------
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.
Celko-BOM.zip
(Access 2000 database)
<<download.gif>>
