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 you answer in the comments so we all can learn from one another.  We also discuss puzzle and more in Essential SQL Learning Group on Facebook.  Be sure to find us there!

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 ID’s, including Names and ProductNumbers, that have no subcomponents
3. Return Product ID’s, including Names and ProductNumbers, that aren’t in a subcomponent
4. List Product ID’s, 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 (green text).

Also, I’ve highlighted in blue 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’.

Read More: SQL Subqueries – The Ultimate Guide >>

List Product ID’s, 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 (green text) to search for product assemblies.

If no ProductAssemblyID’s 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 ID’s, 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 ID’s, 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 (SELECT 1
                   FROM  Production.BillOfMaterials BOM
                   WHERE P.ProductID = BOM.ComponentID
                  )

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.