SQL query based on another table

Question

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 

Everything works fine, but I need a query that will show me similar films in the same genres (in our case, we need similar films for MovieID = 1, which should output MovieID = 3 as the result).

Can you give me an SQL query so that I have a basic idea to do this in order to be able to create more complex queries?

So far my request 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 opinion, Table: TheMovies do not need to do what I ask

Answer 1

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) 

Update:

If you want to find movies that are similar to at least two genres, use this HAVING :

 HAVING COUNT(*) >= 2 

Answer 2

 SELECT M.id FROM TheMovies M LEFT JOIN mMoviesGenres MG ON M.id = MG.movieID LEFT JOIN TheGenres G ON MG.genreID = G.id 

You are in the right proximity to your attempt, but you received it in the wrong order. Start with TheMovies as it contains the identifier you are using. Then add mMoviesGenres as this is the relationship between the two tables you are trying to map. And finally, add to genres that can check which films are related.