Mysql INNER JOIN With Multiple Values-CodePudding

I want to select a video that belongs to two categories.

A video can have several categories.

The result returned by this query and that the videos belong to only one of the categories in the condition

SELECT Videos.id, Videos.title FROM videos Videos 
INNER JOIN categories_videos CategoriesVideos 
    ON Videos.id = CategoriesVideos.video_id 
INNER JOIN categories Categories 
    ON Categories.id = CategoriesVideos.category_id
    AND Categories.id IN(21, 35)
LIMIT 25;

I tried to do several joins on the categories table but it doesn't change anything.

Thanks for your help.

CodePudding user response：

You can try the following approach (could be expanded to test n categories):

SELECT *
FROM videos
WHERE id IN (
    SELECT video_id
    FROM categories_videos
    WHERE category_id IN (21, 35)
    GROUP BY video_id
    HAVING COUNT(*) = 2
)

CodePudding user response：

When you write your join like this

AND Categories.id IN(21, 35)

you're asking for all videos that has at least one of these Categories.id associated with.

If want only the one who has both, you can try this

SELECT Videos.id, Videos.title FROM videos Videos 
INNER JOIN categories_videos CategoriesVideos 
    ON Videos.id = CategoriesVideos.video_id 
INNER JOIN categories Categories 
    ON Categories.id = CategoriesVideos.category_id
AND Categories.id = 21 AND Categories.id =35
LIMIT 25;