SQL: Syntax error with intersect?

This should do what you want:

SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
INNER JOIN Parts AS parts1 ON parts1.pid = Catalog.pid AND parts1.color = "red"
INNER JOIN Parts AS parts2 ON parts2.pid = Catalog.pid AND parts2.color = "green"

Nothing, MySQL doesn't have the INTERSECT keyword. You can rewrite it as an INNER JOIN:

SELECT DISTINCT sid FROM
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "red") a
INNER JOIN
(SELECT Suppliers.sid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Parts.color = "green") b
ON (a.sid = b.sid);

This query can surely be better written, but this is to show that intersect is but merely an inner join with a select distinct, you can automatically transform one into the other.

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).