In this post, you will learn how to explore a SQL bill of materials example by solving a puzzle.
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:
- Write a query to count the number of products not listed in
BillOfMaterials
- Retrieve Product IDs, including Names and ProductNumbers, that have no subcomponents
- Return Product IDs, including Names and ProductNumbers, that aren’t in a subcomponent
- 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 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 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>
You may be wondering why I use subqueries in my answers rather than join
s. 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.