CODEWITHSUNDEEP
sqlmysqlintersectionmysql-error-1064
dito
dito

Reputation: 31

SELECT from two same table with intersect

The query:

SELECT id_user 
  FROM Rating 
 Where id_movie=2 
INTERSECT 
SELECT id_user 
  FROM Rating 
 Where id_movie=3

but I get:

1064 - 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 id_user FROM Rating Where id_movie=3 LIMIT 0, 30' at line 1

any have solution??

Upvotes: 3

Views: 15077

Answers (3)

priyanka.sarkar
priyanka.sarkar

Reputation: 26498

Intersect, Minus keywords are absent in MySql and the workarounds are

  1. Inner Join, and
  2. Subqueries or
  3. Left Join respectively.

Please look into here

Doing INTERSECT and MINUS in MySQL

I have given a shot (though I am a SQL Server guy)

Input:

id_user id_movie
101 1
102 2
102 3
104 4
102 5
107 6
102 2
103 3
109 9
110 2
110 3

The output by using an intersect (if run in SQL Server) will be

id_user
102
110

MySQL compatible queries

Query 1 using Inner join

select distinct a.id_user
from Rating a
join Rating b on a.id_user = b.id_user
where a.id_movie  = 2 and b.id_movie  = 3

Query 2 using Cross join

select distinct a.id_user 
from Rating a, Rating b 
where a.id_user  = b.id_user 
  and a.id_movie  = 2
  and b.id_movie = 3

Query 3 using subquery

Already answered above.

Upvotes: 4

Rob Farley
Rob Farley

Reputation: 15849

How about:

SELECT r2.id_user
FROM Rating AS r2
   JOIN
   Rating AS r3
   ON r3.id_user = r2.id_user
   AND r2.id_movie=2
   AND r3.id_movie=3;

The idea here is that you want to join a row in Rating with another row in Rating, for which the same user has seen movies 2 and 3.

Upvotes: 2

nayakam
nayakam

Reputation: 4249

Following query will do ..

SELECT id_user 
FROM Rating 
Where id_movie=2 and id_user in 
             ( SELECT id_user FROM Rating Where id_movie=3);

Upvotes: 4

Related Questions