title: SQL query records containing all items in list
body: <p>I have the following tables:</p>
  <h2>MOVIES</h2>
  <pre>
    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
  </pre>
  <h2>DIRECTORS</h2>
  <pre>
  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
  </pre>
  <h2>Direct</h2>
  <pre>
    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
  </pre>
  <p>I'd like a query that returns all movies that have directors x, y and z as their director:</p>
  <p>Example:</p>
  <blockquote>
  <p>If I look for a movie with <b>Al Pachino</b> and <b>Clint Eastwood</b>, it
  should return <b>nothing</b> because I don't have a movie that has both of
  them as it's director.</p>
  <p>However if I were to look for a movie with <b>Tim Robbins</b> and <b>Morgan
  Freeman</b>, it should return <b>The Shawshank Redemption</b></p>
  </blockquote>
  <p>Please suggest alternative if it cannot be done using the above design.</p>
  <p>I've attempted it but my query will return results :(</p>
  <pre><code>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')));
  </code></pre>