SQL多对多查询问题

I have three tables: videos, videos_categories, and categories.

我有三个表：视频，视频_类别和类别。

The tables look like this:

表格如下所示：

videos: video_id, title, etc...
videos_categories: video_id, category_id
categories: category_id, name, etc...

In my app, I allow a user to multiselect categories. When they do so, I need to return all videos that are in every selected category.

在我的应用程序中，我允许用户多选类别。当他们这样做时，我需要返回所有选定类别中的所有视频。

I ended up with this:

我最终得到了这个：

SELECT * FROM videos WHERE video_id IN (
    SELECT c1.video_id FROM videos_categories AS c1
    JOIN c2.videos_categories AS c2
    ON c1.video_id = c2.video_id
    WHERE c1.category_id = 1 AND c2.category_id = 2
)

But for every category I add to the multiselect, I have to add a join to my inner select:

但是对于我添加到multiselect的每个类别，我必须在我的内部选择中添加一个连接：

SELECT * FROM videos WHERE video_id IN (
    SELECT c1.video_id FROM videos_categories AS c1
    JOIN videos_categories AS c2
    ON c1.video_id = c2.video_id
    JOIN videos_categories AS c3
    ON c2.video_id = c3.video_id
    WHERE c1.category_id = 1 AND c2.category_id = 2 AND c3.category_id = 3
)

I can't help but feel this is the really wrong way to do this, but I'm blocked trying to see the proper way to go about it.

我不禁觉得这是真正错误的做法，但是我试图找到正确的方法来阻止它。

3 个解决方案

#1

if this is a primary key:

如果这是主键：

 videos_categories: video_id, category_id

then a GROUP BY and HAVING should work, try this:

然后GROUP BY和HAVING应该工作，试试这个：

SELECT
    * 
    FROM videos 
    WHERE video_id IN (SELECT 
                           video_id
                           FROM videos_categories
                           WHERE category_id IN (1,2,3)
                           GROUP BY video_id
                           HAVING COUNT(video_id)=3
                      )

#2

Sounds similar to SQL searching for rows that contain multiple criteria

听起来类似于SQL搜索包含多个条件的行

To avoid having to another join for each category (and hence changing the structure of the query), you can put the categories into a temp table and then join against that.

为了避免对每个类别进行另一次连接（并因此更改查询的结构），您可以将类别放入临时表中，然后再加入该表。

CREATE TEMPORARY TABLE query_categories(category_id int);
INSERT INTO query_categories(category_id) VALUES(1);
INSERT INTO query_categories(category_id) VALUES(2);
INSERT INTO query_categories(category_id) VALUES(3);

SELECT * FROM videos v WHERE video_id IN (
  SELECT video_id FROM video_categories vc JOIN query_categories q ON vc.category_id = qc.category_id
  GROUP BY video_id
  HAVING COUNT(*) = 3
)

Although this is ugly in its own way, of course. You may want to skip the temp table and just say 'category_id IN (...)' in the subquery.

当然，这虽然是以自己的方式丑陋。您可能想要跳过临时表，并在子查询中说“category_id IN（...）”。

#3

Here's a FOR XML PATH solution:

这是一个FOR XML PATH解决方案：

--Sample data
CREATE TABLE Video
    (
    VideoID int,
    VideoName varchar(50)
    )

CREATE TABLE Videos_Categories
    (
    VideoID int,
    CategoryID int
    )

INSERT  Video(VideoID, VideoName)
SELECT  1, 'Indiana Jones'
UNION ALL
SELECT  2, 'Star Trek'

INSERT Videos_Categories(VideoID, CategoryID)
SELECT 1, 1
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 2, 1
GO

--The query
;WITH   GroupedVideos
AS
(
SELECT  v.*,
        SUBSTRING(
                    (SELECT  (', ') + CAST(vc.CategoryID AS varchar(20))
                    FROM     Videos_Categories AS vc
                    WHERE    vc.VideoID = v.VideoID
                    AND      vc.CategoryID IN (1,2)
                    ORDER BY vc.CategoryID
                    FOR XML PATH('')), 3, 2000) AS CatList
FROM    Video AS v
)

SELECT  *
FROM    GroupedVideos
WHERE   CatList = '1, 2'

(Ignore everything below - I misread the question)

Try

尝试

WHERE c1.category_id IN (1,2,3)

or

要么

...
FROM videos v
JOIN Vedeos_categories vc ON v.video_id = vc.video_id
WHERE vc.category_id IN (1,2,3)

Multiple joins aren't at all necessary.

多个连接根本不是必需的。

Edit: to put the solutions in context (I realize it's not obvious):

编辑：将解决方案放在上下文中（我意识到它并不明显）：

SELECT * 
FROM videos 
WHERE video_id IN 
(    SELECT c1.video_id 
FROM videos_categories AS c1
WHERE c1.category_id = IN (1,2,3))

or

要么

SELECT *
FROM videos v
JOIN Vedeos_categories vc ON v.video_id = vc.video_id
WHERE vc.category_id IN (1,2,3)