0

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

Community
  • 1
  • 1
lbrentcarpenter
  • 13
  • 5

1 Answers1

0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Looks good! So if I was to change the list (or in my case generate it programmatically), would I need to change the COUNT to the length of the list? – lbrentcarpenter May 23 '16 at 02:24
  • Great answer. I'm still a newby with sql. I was wondering if my sql statement would work: `SELECT film_id, COUNT(film_id) as actor_count FROM actor_film_mapping GROUP BY film_id WHERE actor_count = (SELECT COUNT(actor_id) FROM actor);` I'm not confident enough in it to post an answer. – Webeng May 23 '16 at 02:29