Who makes all the parts for a given assembly?

from the Artful Common Queries page


One way to arrive at the answer is by asking: What are the assembly-supplier pairs such that no part of the assembly is not made by the supplier? That's relational division again, formulated for two tables by Stephen Todd. Given assemblyparts(assembly,part) and partsuppliers(part,supplier) tables, here is a query that Joe Celko credits to Pierre Mullin.


SELECT DISTINCT 

  AP1.assembly, 

  SP1.supplier

FROM AssemblyParts AS AP1, PartSuppliers AS SP1

WHERE NOT EXISTS (

  SELECT *

  FROM AssemblyParts AS AP2

  WHERE AP2.assembly = AP1.assembly

  AND NOT EXISTS (

    SELECT SP2.part

    FROM PartSuppliers AS SP2

    WHERE SP2.part = AP2.part AND SP2.supplier = SP1.supplier

  )

);




Return to the Artful Common Queries page