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.
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