Find the Supplier number for those suppliers who supply every part

Question

I have the following tables:

Suppliers(Sno, Sname, Address)
Parts(Pno, Pname, Colour)
Catalogue(Sno, Pno, Price)

and I want to find the Sno of the suppliers who supply every part.

So far, I've written this:

SELECT s.sname
FROM suppliers s JOIN catalogue c
USING s.sno

Now how do I write the part "suppliers that supply every part"?

I was thinking about having the count(*) from parts = count(pno) for each supplier Sno. Could someone please give me a hint/write the first part of the equality?

Thanks!

Solution 1

You're close. You need to add a group by/having clause with a subquery:

 group by s.sname having count(*) = (select count(*) from catalogue)

Solution 2

SELECT s.sname
    FROM suppliers s 
        INNER JOIN catalogue c
            ON s.Sno = c.Sno
    GROUP BY s.sname
    HAVING COUNT(c.Pno) = (SELECT COUNT(Pno) FROM Parts)

Solution 3

Off the top of my head, you could write

SELECT  s.Sno
  FROM  suppliers s
  WHERE NOT EXISTS (
     SELECT  p.Pno
       FROM  parts p
       WHERE NOT EXISTS (
         SELECT  c.*
           FROM  catalogue c
           WHERE c.Pno = P.Pno
             AND c.Sno = S.Sno
         )
     )

i.e. supplier where not exists (part that we don't supply), for a solution avoiding counts. No idea if this would be more or less efficient than the counts.