SELECT of two identical tables with intersection

Question

Inquiry:

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 the SQL syntax; check the manual corresponding to the version of MySQL server for the correct syntax to use next to "INTERSECT SELECT id_userFROM Rating Where id_movie= 3 LIMIT 0, 30 'on line 1

anyone has a solution

Answer 1

The next request will be completed.

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

Answer 2

Intersection, Negative Keywords Missing in MySql, and Workarounds

• Internal registration and
• Subqueries or
• On the left, join accordingly.

Please read here

Executing INTERSECT and MINUS in MySQL

( SQL Server)

:

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

( SQL Server)

id_user
102
110

MySQL-

1 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

2 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

3

.

Answer 3

What 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 line in the rating with another line in the rating, for which the same user saw films 2 and 3.