基于其他表的SQL查询

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 in same genres (in our case we need similar movies for MovieID = 1 which should output MovieID = 3 as result).

所有的都很好，但是我需要一个查询，它将显示相同类型的相似电影(在我们的例子中，MovieID = 1需要相似的电影，因此应该输出MovieID = 3)。

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

能不能给我一个SQL查询，这样我就有了一个基本的想法，可以创建更高级的查询?

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 Opinion, the Table: TheMovies is not needed to do what I ask for

**在我看来，桌子:他们不需要做我要求的事

2 个解决方案

#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 which are similar with regard to at least two genres, then use this HAVING clause:

如果你想找到至少两种类型相似的电影，可以使用“拥有”条款:

HAVING COUNT(*) >= 2

#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 correct vicinity with your attempt, but you got it somewhat in the wrong order. Start with TheMovies as it contains the ID you are after. Then add in mMoviesGenres as it is the link between the two tables you are trying to match. And lastly add in the Genres, which will be able to check which movies that are related.

你的尝试是正确的，但是你的顺序有点错误。从TheMovies开始，因为它包含了您所追求的ID。然后添加mmovies流派，因为它是您要匹配的两个表之间的链接。最后加入类型，可以检查哪些电影是相关的。

#1