PHP Lover PHP Lover - 4 days ago 6
MySQL Question

SQL Query based on other table

I have a normalized table:

`Table: TheMovies`
id | MovieName
---------------------
1 | Zootopia
2 | Moana
3 | Toy Story

`Table: TheGenres`
id | GenreName
---------------------
21 | Action
22 | Animation
23 | Adventure

`Table: mMoviesGenres`
movieID | genreID
---------------------
1 | 21
1 | 23
2 | 22
2 | 21
3 | 23
3 | 21


All works fine, but I need a query which will show me similar movies based on exactly same genres (in our case we need similar movies for MovieID = 1 which should output MovieID = 3 as result).

Can you give me an SQL query so I have a basic idea of doing that, to be able to create more advanced queries?

My query, so far, is:

SELECT
TheMovies.*
FROM
mMoviesGenres
JOIN TheMovies ON mMoviesGenres.movieID = TheMovies.id
WHERE
mMoviesGenres.genreID IN
(
SELECT
genreID
FROM
mMoviesGenres
WHERE
movieID = 1
)


** In my Oppinion, the Table: TheMovies is not needed to do what I ask for...

Answer

Try this query:

SELECT m2.movieId
FROM mMoviesGenres m1
INNER JOIN mMoviesGenres m2
    ON m1.genreID = m2.genreID
WHERE m1.movieId = 1 AND
      m2.movieId <> 1
GROUP BY m2.movieId
HAVING COUNT(*) = (SELECT COUNT(*) FROM mMoviesGenres WHERE movieId = 1)
Comments