How to code SQL for a BOM Explosion?

If I have a table of finished goods named Product like this:

ProductID Name
======== ====

1                 Product A
2                 Product B
3                 Product C
4                 Product D

and a components table like this:

ProductID PartID Count
======== ===== =====

1                 2            1
1                 3            2
3                 4            3

I would like to report that "Product A" is composed of 1 piece of "Product B" and 6 pieces of "Product D" (ie 2 pieces of "Product C" each of which is itself composed of 3 pieces of "Product D".

There is no fixed depth to the recursion.

Is there a way to write a SELECT statement for this where a can avoid procedures? Is this where I could use the "connect by ... prior..." clause? Is there a way to do it with "standard" sql? Or perhaps the two table structure of "Product" and "Component" is not the most elegant approach?

Thanks in advance for any help
-Glenn Received on Thu Aug 19 1999 - 06:12:17 CDT