Use Subquery with Bill of Materials

In this puzzle, we’re going to learn how to work with the AdventureWorks Bill of Materials table. Companies use a BOM (Bill of Materials) to itemize the products that are used or contained in another product.

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.

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. List Product ID’s, including Names and ProductNumbers, that have no subcomponents
3. List 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.
• The items in green are sub components of the Bike product.
• The items in green are also product assemblies, as there are other components that are used to make them.
• The items in blue are parts that don’t have any subcomponents.

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

To find out the number of products not listed in the bill of materials, 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).

You’ll see the results are also restricted to products current being sold and not discontinued (italics).

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 is correlated with the main query via ProductID. Meaning, for each Product, a query is run to find BOM entries whose ComponentID or ProductAssemblyID matches the Product.ProductID.

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.

Since a subcomponent is any product used within another, we can easily infer that a product doesn’t have subcomponents if the product isn’t listed in the BOM table as an assembly. You’ll see we used a subquery (italics) to search for product assemblies. The NOT EXISTS returns TRUE if no ProductAssemblyIDs are found.

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. When found, the EXISTS returns TRUE and the product is included in the result.

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