Repetition of query to produce report

Question

I am creating a bill of materials program.

There are two main tables named Products and Sub_Products.

In the Products table, the fields are (Product_Name, Code). In the Sub_Products table, the fields are (Code, Sub_Name).

The tables are linked with code, i.e.: one product is made up of many sub_products, each sub_product is a product as well, making it have many sub_products.

I have created a query that reads a product and gets its sub_products. I need a query to compare Sub_Name with Product_Name and then check more sub_products, continuing until no more sub_products are found.

Any ideas?

Answer 1

I guess you will have to use a script rather than SQL query to loop through them. Assuming that the products can be nested more than 3 levels.

Answer 2

I've been working on this exact problem in an ASP.NET MVC application. A function that gathered all the subproducts for each product and recursed on each subproduct worked well. We have some BOMs that are 15 levels deep.