views:

224

answers:

3

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

+1  A: 

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"
Hogan
I don't think that's the problem in this case. `INTERSECT` simply isn't supported by MySQL.
Roland Bouman
And that query won't work out. The OP wants all sids where they have a green AND a red part. Your query returns all sids with either a green OR a red part.
Vinko Vrsalovic
@Roland, @Vinko : Yeah I saw that, I fixed the answer, this should do what he wants and be faster than a subquery.
Hogan
A: 

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

Roland Bouman
It would be interesting to see which is faster, this query or my query -- I think it comes down to which is faster, a 2nd join or a group by operation.
Hogan
@Hogan, *shrug*. This is dependent upon so many things: indexes, which engine, how many rows, how many memory. enless list...if the question would have been to find the fastest solution, I would have taken an entirely differernt approach to answering this. For now, I am in the mode of: how to rewrite standard SQL to get an equivalent result in case MySQL doens't happen to support the syntax.
Roland Bouman
@Roland: point taken, I guess I'm always in the mode of what is fastest, not what is clearest.
Hogan
+2  A: 

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.

Vinko Vrsalovic
I mostly agree, but `INTERSECT` is not exactly the same as an `INNER JOIN`. `INTERSECT` defaults to `INTERSECT DISTINCT`, which means it is required to return only unique rows. In practical cases and probably in this case too, it would just work, but to get a true rewrite, you woudl have to add either `DISTINCT` or `GROUP BY`
Roland Bouman
@Roland: Thanks for the note, I edited to reflect it.
Vinko Vrsalovic
all cool. You're geting +1 now, because i basically think `JOIN` is much, much nicer than `INTERSECT` :) Only time I ever saw intersect, is in SQL homework assignments ;)
Roland Bouman
thanks. what are some other ways I can improve this query?
Rosarch
For example, the two other answers here which are built as a single query and have less total joins.
Vinko Vrsalovic