CODEWITHSUNDEEP
sqlmysqlintersect
Nick Heiner
Nick Heiner

Reputation: 122432

SQL: Syntax error with intersect?

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

Upvotes: 15

Views: 35660

Answers (6)

Khushboo Kumari
Khushboo Kumari

Reputation: 1

I think SQL doesn't support INTERESCT

try this one

SELECT DISTINCT
s.sid
FROM
 suppliers s,
 catalog c
WHERE
 s.sid = c.sid
 AND c.pid IN (SELECT 
 p1.pid
 FROM
 parts p1,
 parts p2
 WHERE
 p1.color = 'red' AND p2.color = 'green');

Upvotes: 0

Haresh Kumar
Haresh Kumar

Reputation: 539

Description:

enter image description here

Suppose we have two DB tables T1 and T2 and we need common elements from both tables then we can use INTERSECT Operation as shown in the above image.

In MySql there is no INTERSECT operator. So we can implement INTERSECT concept with following two operators:

1. IN Clause
We can use IN clause when we want one column as of result of INTERSECT Operation.

2. EXISTS Clause
We can use EXISTS clause when we want multiple column as of result of INTERSECT Operation.

For more details you can read the documentation here.

Answer:


In above asked question they want single column as of the result, so we can use IN Clause as follows:

SELECT Suppliers.sid FROM Suppliers 
INNER JOIN Catalog ON Catalog.sid = Suppliers.sid 
INNER JOIN Parts ON Parts.pid = Catalog.pid 
WHERE Parts.color = "red"
AND Suppliers.sid IN (
    SELECT Suppliers.sid
    FROM Suppliers
    INNER JOIN Catalog ON Catalog.sid = Suppliers.sid
    INNER JOIN Parts ON Parts.pid = Catalog.pid
    WHERE Parts.color = "green")

Upvotes: 0

Roland Bouman
Roland Bouman

Reputation: 31961

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

Upvotes: 14

Jasurbek Nabijonov
Jasurbek Nabijonov

Reputation: 1745

Another solution in order to use INTERSECT in MySQL is to use IN clause. Problem: "Find course id’s of 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 on IN clause , please search on Google.

Upvotes: 2

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340191

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.

Upvotes: 6

Hogan
Hogan

Reputation: 70513

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"

Upvotes: 2

Related Questions