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