MYSQL Multiple Select For Same Category?

I have 3 tables (scenes, categories, scenes_categories ) in a many to many relationship.

scenes ( id, title, description ) categories ( id, title ) scenes_categories ( scene_id, category_id )

I'm having problems making a query to select scenes that must match multiple categories. For example, I might want to select scenes that match category 3 AND category 5 AND category 8, but I can't figure out how to get this to work.

So far I've got something like

SELECT scenes.id, scenes.title, scenes.description
FROM scenes
LEFT JOIN scenes_categories ON scenes.id = scenes_categories.scene_id
LEFT JOIN categories ON scenes_categories.category_id = categories.id
WHERE scenes_categories.category_id = '3'
AND scenes_categories.category_id = '5'
AND scenes_categories.category_id = '8'
AND scenes.id = '1'

How can I select for records that must match all the category ID's specified?

From stackoverflow

• You need to require that a row exists in your many-to-many table for that sceneId, for each categoryId you are requiring: So try this:

  SELECT s.id, s.title, s.description
  FROM scenes s
  WHERE s.id = '1'
     And Exists (Select * From scenes_categories 
                 Where scene_id = s.Id
                    And category_id = '3')
     And Exists (Select * From scenes_categories 
                 Where scene_id = s.Id
                    And category_id = '5')
  
     And Exists (Select * From scenes_categories 
                 Where scene_id = s.Id
                    And category_id = '8')
  

  another option that should work is to do three inner joins instead:

  SELECT s.id, s.title, s.description
  FROM scenes s
    Join scenes_categories c3 
        On c3.scene_id  = s.Id
             And c3.category_id ='3'
    Join scenes_categories c5 
        On c3.scene_id  = s.Id
             And c3.category_id ='5'
    Join scenes_categories c8 
        On c3.scene_id  = s.Id
             And c3.category_id ='8'     
  WHERE s.id = '1'
  

  Zoidberg : Pretty sure he was looking for a dynamic solution here.

  Zoidberg : Sorry, I took off my downvote... didn't realize he didn't want an OR relationship

  Charles Bretana : @zoid, np, thx for 2nd look!

  waywardspooky : Thank you so much! I implemented the second solution you suggested and it worked like a charm; albeit it took me some time to figure out exactly how it works conceptually, haha.

• Charles Bretana's answer will work, but might want to check the performance of that against this to see which works better for you.

  SELECT * FROM scenes
  INNER JOIN (
    SELECT scene_id
    FROM scenes_categories
    WHERE category_id IN (3,5,8)
    GROUP BY scene_id
    HAVING count(*) = 3
  ) valid ON scenes.id = valid.scene_id
  

  Assuming your SQL is dynamic, this will probably be a bit easier to implement.