Figure 8 Details on ProductID 210
　

WITH BOMCTE(ProductID, Qty)
AS
(
  SELECT ComponentID, PerAssemblyQty
  FROM BillOfMaterials
  WHERE ComponentID = 210
    AND AssemblyID IS NULL
    AND ObsoleteDate IS NULL

  UNION ALL

  SELECT BOM.ComponentID, BOMCTE.Qty * BOM.PerAssemblyQty
  FROM BOMCTE
    JOIN BillOfMaterials AS BOM
      ON BOM.ObsoleteDate IS NULL
      AND BOM.AssemblyID = BOMCTE.ProductID
)
SELECT B.ProductID, P.Name, B.Qty
FROM BOMCTE AS B
  JOIN Product AS P
    ON P.ProductID = B.ProductID
ORDER BY P.ProductID;