CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designrelational-database
Rami
Rami

Reputation: 61

How to implement that SQL query in mysql?

I am having a hard time implementing the following SQL query in mysql.

i am trying to find For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.

my SQL query is 

SELECT S.sname , count(*)
FROM Suppliers S,Parts P,Catalog C
WHERE P.pid = C.pid AND C.sid = S.sid
GROUP BY S.sname , S.sid
HAVING EVERY (P.color = 'green')

any suggestions on how to implement that in mysql will be greatly appreciated

Upvotes: 1

Views: 292

Answers (2)

Edper
Edper

Reputation: 9322

You use HAVING if you are using the aggregate function itself to compare. Use also JOIN not the old school multiple-table comma separated because that could have unintended consequence.

SELECT S.sname , count(*) as TotalParts
FROM Suppliers S
INNER JOIN Catalog C
ON S.sid = C.sid
INNER JOIN Parts P
ON P.pid = C.pid
WHERE   P.color = 'green'
GROUP BY S.sname

Upvotes: 0

Mureinik
Mureinik

Reputation: 312219

I think using the NOT EXISTS construct is the easiest way to go:

SELECT s.sname, COUNT(*)
FROM   suppliers s
WHERE  NOT EXISTS (SELECT 1 
                   FROM   parts p
                   JOIN   catalog c ON p.pid = c.pid
                   WHERE  c.sid = s.side AND p.color != 'green')

Upvotes: 2

Related Questions