SQL query records containing all items in list

Question

I have the following tables:

MOVIES

  MOVIE_ID TITLE
---------- -----------------------------
         1 The Shawshank Redemption
         2 The Godfather
         3 The Godfather: Part II
         4 The Dark Knight
         5 Pulp Fiction
         6 The Good, the Bad and the Ugly
         7 Schindler's List
         8 Angry Men
         9 Fight Club
        10 Inception
        11 Forrest Gump

DIRECTORS

DIRECTOR_ID NAME
----------- -------------------------
          1 Tim Robbins
          2 Morgan Freeman
          3 Marlon Brando
          4 Al Pachino
          5 Robert De Niro
          6 Christian Bale
          7 Heath Ledger
          8 John Travola
          9 Uma Thurman
         10 Clint Eastwood
         11 Eli Wallach

Direct

  MOVIE_ID DIRECTOR_ID
---------- -----------
         1           1
         1           2
         2           3
         2           4
         3           4
         3           5
         4           6
         4           7
         5           8
         5           9
         6          10

I'd like a query that returns all movies that have directors x, y and z as their director:

Example:

If I look for a movie with Al Pachino and Clint Eastwood, it should return nothing because I don't have a movie that has both of them as it's director.

However if I were to look for a movie with Tim Robbins and Morgan Freeman, it should return The Shawshank Redemption

Please suggest alternative if it cannot be done using the above design.

I've attempted it but my query will return results :(

SELECT m.title FROM Movie m 
WHERE m.movie_id IN (
      SELECT d.movie_id FROM Direct d
      WHERE d.director_id IN (
           SELECT director_id FROM Director dir 
           WHERE name IN('Clint Eastwood', 'Al Pachino')));

Answer 1

This is one way to achieve this, viz to group, filter and then count the directors:

SELECT m.title 
FROM Movie m
     INNER JOIN Direct md
     on md.movie_id = m.movie_id
     INNER JOIN Directors d
     on md.director_id = d.director_id
WHERE 
     d.name IN('Clint Eastwood', 'Al Pachino')
GROUP BY m.title
HAVING COUNT(DISTINCT d.director_id) = 2;

SqlFiddle here

{Out of interest, aren't these the actors in the movies?}