CODEWITHSUNDEEP
sqlms-accessjoingroup-bycount
Kosarar
Kosarar

Reputation: 151

Find the names of the suppliers who supply all the parts in MS Access

I have three tables:

SPB (ID, Part, Supplier), Suppliers (ID, Name), Parts (ID, Part)

I want to find the names of the suppliers who supply all the parts in MS Access.

This code does not work properly:

SELECT Suppliers.ID, Parts.ID
FROM Suppliers INNER JOIN (Parts INNER JOIN SPB ON Parts.ID = SPB.Part) ON Suppliers.ID = SPB.Supplier
WHERE 
 SPB.Part IN 
 (SELECT SPB.Part
 FROM SPB
 WHERE Suppliers.ID = SPB.Supplier)
AND
 SPB.Part IN 
 (SELECT Parts.ID
 FROM Parts
 WHERE Parts.ID)

What could be done better?

Upvotes: 0

Views: 1741

Answers (2)

forpas
forpas

Reputation: 164174

You need a CROSS join of Suppliers and Parts and a LEFT join to SPB.
Then you group by supplier and set the condition in the HAVING clause:

SELECT t.supplierID, t.name
FROM (
  SELECT s.ID AS supplierID, s.name, p.Id AS part_ID
  FROM Suppliers AS s, Parts AS p
) AS t LEFT JOIN SPB AS b ON b.Supplier = t.supplierID AND b.Part = t.part_ID
GROUP BY t.supplierID, t.name
HAVING COUNT(*) = COUNT(b.Part)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270703

Assuming that SPB has one row per supplier/part pair, then you can count:

select supplier
from spb
group by supplier
having count(*) = (select count(*) from parts);

If you can have duplicate supplier/part pairs, then just remove duplicates before checking:

select supplier
from (select distinct supplier, part
      from spb
     ) as sp
group by supplier
having count(*) = (select count(*) from parts);

Upvotes: 2

Related Questions