Geek Answers Handbook

SQL: Syntax error with intersection?

This is my request:

-- 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 mistake:

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

What am I doing wrong?

This is the diagram:

Providers ( sid: integer , sname: string, address bar)

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

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

bold = primary key

+6
sql mysql intersect
source share
4 answers

MySQL, which you seem to be using, does not support the INTERSECT syntax. You will have to solve it differently.

In this case, this is trivial - we only need a list of all suppiers that offer the "green" and "red" parts of any part - your request does not worry about whether the parts are connected, so we can solve this quite easily:

 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 think the original query is a typical INTERSECT problem. Take a look at the JOIN solution offered by Vinko Vrsalovic for the generic INTERSECT emulation INTERSECT (which I would prefer even if the DBMS really offers INTERSECT initially)

+6
source share

Nothing, MySQL does not have the keyword INTERSECT. 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 certainly be better written, but it should show that the intersection is just an internal connection with a separate one, you can automatically convert it to another.

+4
source share

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"
+2
source share

Another solution for using INTERSECT in MySQL is to use IN . Problem: "Find courses offered in fall 2009 and spring 2010"

 //DML sample (select course_id from section where semester = 'Fall' and year = '2009') intersect (select course_id from section where semester = 'Spring' and year = '2010');

In MySQL:

 select distinct course_id from section where semester = 'Fall' and year= 2009 and course_id in (select course_id from section where semester = 'Spring' and year= 2010);

If you need more in IN , do a Google search.

0
source share

More articles:


All Articles