I am using MySQL. Here is my schema:
Suppliers(sid: integer, sname: string, address string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
(primary keys are bolded)
I am trying to write a query to select all parts that are made by at least two suppliers:
-- Find the pids of parts supplied by at least two different suppliers. SELECT c1.pid -- select the pid FROM Catalog AS c1 -- from the Catalog table WHERE c1.pid IN ( -- where that pid is in the set: SELECT c2.pid -- of pids FROM Catalog AS c2 -- from catalog WHERE c2.pid = c1.pid AND COUNT(c2.sid) >= 2 -- where there are at least two corresponding sids );
First off, am I even going about this the right way?
Secondly, I get this error:
1111 – Invalid use of group function
What am I doing wrong?