CODEWITHSUNDEEP
mysqlsql
jsmars
jsmars

Reputation: 1928

How do find common items and get their individual user values in MySQL?

I have three tables: Users, Movies, Ratings. How do I create a MySQL query that returns a list of movies that two specified users have seen, giving both information about the movie and both users seperate rating?

I have created a SQLFiddle here with some example databases

Expected result for users A=1 and B=2: Both have seen The Shining and Interstellar so they each have one entry with movie info plus both users ID and ratings are in the results. Other movies and ratings are not included.

+----+-------+-------+---------+---------+--------------+
| id | UserA | UserB | RatingA | RatingB | Name         |
+----+-------+-------+---------+---------+--------------+
|  1 |     1 |     2 |      10 |       9 | The Shining  |
|  2 |     1 |     2 |       9 |       7 | Interstellar |
+----+-------+-------+---------+---------+--------------+

Upvotes: 0

Views: 21

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You need Self JOIN, use alias to difference between each user..

SQL DEMO

SELECT u1.*, u2.*, m.Name
FROM rating u1
JOIN rating u2
  ON u1.MovieID = u2.movieID
 AND u1.UserID = 1
 AND u2.UserID = 2
JOIN Movies m
  ON u1.MovieID = m.ID

Upvotes: 1

Related Questions