how to write a recursive query?

I am wondering if there is some type of recursive query to return the values I want from the following database.

Here is the setup:

The client builds reptile cages.

Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.

The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.

PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.

Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb

Here is a quick schema:

Table: PRODUCT
--------------------------
PRODUCTID PK
PRODUCTNAME nVarChar(30)


Table: ASSEMBLY
--------------------------
PRODUCTID PK (FK to PRODUCT.PRODUCTID)
COMPONENTID PK (FK to PRODUCT.PRODUCTID)
QTY INT


I can write a query that takes the PRODUCTID, and returns all



PRODUCT
=======
PRODUCTID PRODUCTNAME
--------- -----------
1 Cage Assembly - Solid Sides
2 Cage Assembly - Split Back
3 Cage Assembly - Split Sides
4 Cage Assembly - Split Top/Bottom
5 Cage Assembly - Split Back and Sides
6 Cage Assembly - Split Back and Top/Bottom
7 Cage Assembly - Split Back and Sides and Top/Bottom
8 33S - Aluminum Divider
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
12 Connector Socket
13 Connector Screws



ASSEMBLY
=========
PRODUCTID COMPONENT QTY
--------- --------- ---
1 9 8
1 10 4
1 11 1
2 1 1
2 8 1
3 1 1
3 8 1
4 1 1
4 8 1
5 1 1
5 8 2
6 1 1
6 8 2
7 1 1
7 8 3
11 12 8
11 13 8



I need a query that will give me all parts for each PRODUCT.

Example: I want all parts for the PRODUCT "Cage Assembly - Split Back"

The results would be:


PRODUCTID PRODUCTNAME
--------- -----------
2 Cage Assembly - Split Back
1 Cage Assemble - Solid Back
9 33C - Aluminum Frame
10 T3C - Door Frame
11 Connector Kit
8 33S - Aluminum Divider
12 Connector Socket
13 Connector Screws

Is it possible to write such a query or stored procedure?

in a specific case, yes, if you know in advance how many levels down the hierarchy of assemblies/parts you need to go, you would write a left outer join query with as many joins as the maximum number levels you need to traverse to find all component parts for the given part

in the general case, where this number of levels is not known in advance, no, you can't write a query for this

however, you could write a stored proc, but note that the stored proc would be running a query inside a loop and building up its results in a temp table