mysql - SQL: Syntax error with intersect?

Question

This is my query:

-- Sids of suppliers who supply a green part AND a red part
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "red")
INTERSECT
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "green");

This is the error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "INTERSECT (SELECT Suppliers.sid FROM Suppliers JOIN Catalog ON Catalog.sid = Sup" on line 6.

What am I doing wrong?

This is the schema:

Suppliers(sid: integer, sname: string, address string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

bold = primary key

Answer 1

MySQL, which you appear to be using, does not support the INTERSECT syntax. You're going to have to solve it another way.

In this case, it is trivial -we only need a list of all suppliers that offer "green" and "red" of some part- your query does not bother to see if the parts themselves are related, so we can solve it quite easily like this:

SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color IN ('red', 'green')
GROUP BY Suppliers.sid
HAVING COUNT(DISTINCT Parts.color) = 2

Personally, I don't believe the original query is a typical INTERSECT problem. Take a look at the JOIN solution offered by Vinko Vrsalovic for a general solution to emulate the INTERSECT (which I would btw prefer even if the RDBMS would in fact offer INTERSECT natively).