lbrentcarpenter lbrentcarpenter - 6 months ago 17
MySQL Question

Select rows in table A that junction to all of a set of items from table B

Based on this image (forget "genre" though):

I have a list of actors, and need to retrieve a list of films that contain every actor on my list.

Example data:

INSERT INTO actor (actor_id, name) VALUES
(1, "Alice"), (2, "Bob");

INSERT INTO film (film_id, title) VALUES
(1, "Star Wars"), (2, "The Matrix"), (3, "Lion King);

INSERT INTO actor_film_mapping (actor_id, film_id) VALUES
(1, 1),
(1, 2), (2, 2),
(2, 3);


Looking for e.g. List of films containing Alice and Bob should give me only The Matrix

The closest I've come is this query which selects films with any of my list of actors, rather than ones with all.

Looking on StackOverflow for similar answers, I got this which works fine in MSSQL but not MySQL.

Hope I've phrased this question OK. I'm new to StackOverflow

Answer

You are pretty close. Instead of using select distinct, do an aggregation and count the number of matches using having:

SELECT f.*
FROM film f INNER JOIN
     actor_film_mapping afm
     ON f.film_id = afm.film_id
WHERE afm.actor_id IN (1, 2)
GROUP BY f.film_id
HAVING COUNT(*) = 2;
Comments