SQL Many-to-Many Query Problem

Question

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:

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.

Answer 1

if this is a primary key:

 videos_categories: video_id, category_id

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

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
                      )