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.




FROM AssemblyParts AS AP1, PartSuppliers AS SP1



  FROM AssemblyParts AS AP2

  WHERE AP2.assembly = AP1.assembly


    SELECT SP2.part

    FROM PartSuppliers AS SP2

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



Return to the Artful Common Queries page