SELECT from two same table with intersect

Question

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??

Answer 1

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

Answer 2

I've never used INTERSECT with MySQL, I've only seen that with MS-SQL

You should be able to do something like:

select id_user from (select id_user from Rating where id_movie = 2) tbl1 natural join (select id_user from Rating where id_movie = 3) tbl2;

Answer 3

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.

Answer 4

Intersect, Minus keywords are absent in My Sql and the work arounds are a) InnerJoin and b) Subqueries or LeftJoin 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 insersect(if run in Sql Server) will be

id_user
102
110

MY SQL 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 Subquey

Already answered above.