![]() The subquery is correlated with the main query via ProductID. WHERE BOM.ProductAssemblyID = p.ProductID ( SELECT 1 FROM Production.BillOfMaterials BOM WHERE P.SellEndDate is NULL AND p.DiscontinuedDate is NULL AND NOT EXISTS You'll see the results are also restricted to products current being sold and not discontinued (blue text). 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). 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. You'll be ready to learn SQL Server in no time! Write a Query to Count the Number of Products not Listed in BillOfMaterials It helps you get a free version of SQL Server all setup with AdventureWorks. Note: If you're wondering how to work with the AdventureWorks database, then check out my Getting Started with SQL guide. The items in blue are parts that don't have any subcomponents. ![]() ![]() The items in green are also product assemblies, as there are other components that are used to make them.The items in green are sub components of the Bike product.The items in yellow are products that aren't in a subcomponent.If you need help understanding how a BOM works, please check out the following diagram: List Product IDs, including Names and ProductNumbers, that are a subcomponent.List Product IDs, including Names and ProductNumbers, that aren’t in a subcomponent.List Product IDs, including Names and ProductNumbers, that have no subcomponents.Write a query to count the number of products not listed in BillOfMaterials.Using the AdventureWorks database and diagram above, provide queries for the following: We also discuss the puzzle and more in Essential SQL Learning Group on Facebook. Once you have figured out the puzzle, post your answer in the comments so we all can learn from one another. Nothing beats practicing what you've learned. Solving puzzles is a great way to learn SQL. Companies use a BOM (Bill of Materials) to itemize the products that are used or contained in another product. In this puzzle, we're going to learn how to work with the AdventureWorks Bill of Materials table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |