SQL Bill of Materials Example

In this puzzle, we’ll explore a SQL bill of materials example. Companies use a BOM (Bill of Materials) to itemize the components and sub assemblies used to construct their products.

Solving puzzles is a great way to learn SQL. Nothing beats practicing what you’ve learned. Once you have figured out the puzzle, post your answer in the comments so we all can learn from one another.

Bill of Materials Problems

Using the AdventureWorks database and diagram above, provide queries for the following:

1. Write a query to count the number of products not listed in BillOfMaterials
2. Retrieve Product IDs, including Names and ProductNumbers, that have no subcomponents
3. Return Product IDs, including Names and ProductNumbers, that aren’t in a subcomponent
4. List Product IDs, including Names and ProductNumbers, that are a subcomponent

If you need help understanding how a BOM works, please check out the following diagram:

The items in:

• yellow are products that aren’t in a subcomponent.
• green are sub components of the Bike product.
• blue are parts that don’t have any subcomponents.

Hang in there! By the time you get done with these SQL bill of materials examples, you’ll be a pro!

Write a Query to Count the Number of Products Not Listed in BillOfMaterials

To know the number products not list on the BOM, we need to find out how many products are in the aren’t listed in the BOM as a product assembly or component .

One way to do this is to use a subquery in the where clause and look for ProductID references that don’t exist in either the BillofMaterial table ProductAssemblyID or ComponentID columns (bold text).

Also, I’ve highlighted in bold the SQL to not include discontinued or inactive products.

The query to do so is:

SELECT Count(1)
FROM   Production.Product P
WHERE  P.SellEndDate is NULL
       AND p.DiscontinuedDate is NULL
       AND NOT EXISTS
       (SELECT 1
        FROM   Production.BillOfMaterials BOM
        WHERE  BOM.ProductAssemblyID = p.ProductID
               OR BOM.ComponentID = p.ProductID
       )

This returns the scalar value 157.

The subquery and main query are correlated via ProductID. A match by ProductID is made to the BOM’s components and assemblies for each Product.

To do this, we run a query for each product. We match the product’s ProductID to BOM entries. It is included in the result if it matches a ComponentID or ProductAssemblyID’.

List Product IDs, including Names and ProductNumbers, that have no subcomponents

To answer this question, we take our query from the previous answer alter it to display product column values, rather than a row count.

We can easily infer that a product doesn’t have subcomponents if the product isn’t listed in the BOM table as an assembly, since a subcomponent is any product used within another one. You’ll see we used a subquery (bold text) to search for product assemblies.

If no ProductAssemblyIDs are found, the NOT EXISTS operator returns TRUE.

SELECT P.ProductID,
       P.Name,
       P.ProductNumber,
       P.FinishedGoodsFlag,
       P.ListPrice
FROM   Production.Product P
WHERE  P.SellEndDate is NULL
       AND p.DiscontinuedDate is NULL
       AND NOT EXISTS (SELECT 1
                       FROM  Production.BillOfMaterials BOM
                       WHERE P.ProductID = BOM.ProductAssemblyID)

List Product IDs, including Names and ProductNumbers, that aren’t in a subcomponent

The main difference in answering this question versus the previous lies in the subquery used to probe the BillOfMaterials table.

For this question, we’re concerned whether it is not a subcomponent. To do so, we alter our subquery to search for BOM entries whose ComponentID matched the ProductID. Finding a match here signifies our product is a subcomponent. Of course, we’re looking for the opposite, so we use the NOT EXISTS qualifier to make it so.

SELECT P.ProductID,
       P.Name,
       P.ProductNumber,
       P.FinishedGoodsFlag,
       P.ListPrice
FROM   Production.Product P
WHERE  P.SellEndDate is NULL
       AND p.DiscontinuedDate is NULL
       AND NOT EXISTS (SELECT 1
                       FROM  Production.BillOfMaterials BOM
                       WHERE P.ProductID = BOM.ComponentID)

List Product IDs, including Names and ProductNumbers, that are a subcomponent

The answer to this question is just the opposite to that of the previous one. So, in this case, the subquery searches for BOM entries whose ComponentID matched the ProductID.

SELECT P.ProductID,
       P.Name,
       P.ProductNumber,
       P.FinishedGoodsFlag,
       P.ListPrice
FROM   Production.Product P
WHERE  P.SellEndDate is NULL
       AND p.DiscontinuedDate is NULL
       AND EXISTS <span style="color: #008000;">(SELECT 1
                   FROM  Production.BillOfMaterials BOM
                   WHERE P.ProductID = BOM.ComponentID
                  )</span>

Final Comments on AdventureWorks Bill of Materials

You may be wondering why I use subqueries in my answers rather than joins. I did so since I think in this sort of problem, the subquery is easier to read. Since we’re testing for existence, the EXISTS clause lends nicely to this, and may be easier to interpret.

Also, by using subqueries, I’m also including products that don’t have any entries in the BOM table. If my answer used INNER JOINS, then by virtue they only include matching rows, these products which don’t have bill of materials would have been excluded.

Of course, I could have use Outer JOINS and tested for NULL to skirt the issue, but for some reason, I try to avoid outer joins when I can. That’s just my preference, and not necessarily a best practice.