Exercise in SQL with complex subquery

Question

I'm trying to do a query in SQL. The database is the one of IMBD one. This is the form:

So, I need the actors/actress from the film Pulp Fiction(id=2175869) that never acted jointly in no other film with another actor / actress of Pulp Fiction.

It should be something like:

SELECT person_id FROM cast_info WHERE movie_id = 2175869 AND
person_id NOT IN (SELECT )

But I can't figure it out how to do the second part.

Answer 1

Query used later as sub-query, returning movies other than Pulp Fiction where more than one actor of Pulp Fiction played in them

SELECT movie_id
FROM cast_info
WHERE
    movie_id <> 2175869 AND
    person_id IN (SELECT person_id FROM cast_info WHERE movie_id = 2175869)
GROUP BY movie_id
HAVING COUNT(*) > 1

Now get persons playing in Pulp Fiction who never played in such a movie

SELECT person_id
FROM cast_info
WHERE
    movie_id = 2175869 AND
    person_id NOT IN (
        SELECT person_id FROM cast_info WHERE movie_id IN (
            SELECT movie_id
            FROM cast_info
            WHERE
                movie_id <> 2175869 AND
                person_id IN (SELECT person_id FROM cast_info WHERE movie_id = 2175869)
            GROUP BY movie_id
            HAVING COUNT(*) > 1
        )
    )

Note: I am assuming that one actor can be casted only once per movie. If the same actor can be casted for different roles in the same movie, then replace COUNT(*) by COUNT(DISTINCT person_id).

Answer 2

First you need to see all actors from pulp fiction:

SELECT person_id  as pulp_actor_id
FROM cast_info
WHERE movie_id = 2175869

Then you need to find all the actor duos:

SELECT c1.person_id as actor1_id
       c2.person_id as actor2_id
FROM cast_info c1
JOIN cast_info c2
  ON c1.person_id < c2.person_id
WHERE c1.movie_id = 2175869
  AND c2.movie_id = 2175869;

Find all the movies where duo act together beside pulp fiction.

SELECT actor1_id, actor2_id, actor1_movies.movie_id
FROM (SELECT c1.person_id as actor1_id
             c2.person_id as actor2_id
      FROM cast_info c1
      JOIN cast_info c2
        ON c1.person_id < c2.person_id
      WHERE c1.movie_id = 2175869
        AND c2.movie_id = 2175869) as duos
JOIN cast_info as actor1_movies
  ON duos.actor1_id = actor1_movies.person_id
 AND actor1.movie_id <> 2175869
LEFT JOIN cast_info actor2_movies
  ON duos.actor2_id = actor2_movies.person_id
 AND actor1_movies.movie_id = actor2_movies.movie_id
WHERE actor2.movie_id IS NOT NULL;

Now get all the actors from pulp fiction not in the last result

SELECT person_id as pulp_actor_id
FROM cast_info c
LEFT JOIN ( SELECT actor1_id, actor2_id, actor1_movies.movie_id
            FROM (SELECT c1.person_id as actor1_id
                         c2.person_id as actor2_id
                  FROM cast_info c1
                  JOIN cast_info c2
                    ON c1.person_id < c2.person_id
                  WHERE c1.movie_id = 2175869
                    AND c2.movie_id = 2175869) as duos
            JOIN cast_info as actor1_movies
              ON duos.actor1_id = actor1_movies.person_id
             AND actor1.movie_id <> 2175869
            LEFT JOIN cast_info actor2_movies
              ON duos.actor2_id = actor2_movies.person_id
             AND actor1_movies.movie_id = actor2_movies.movie_id
            WHERE actor2.movie_id IS NOT NULL;
            WHERE movie_id = 2175869 ) as duos
  ON c.person_id IN ( actor1_id, actor2_id )
WHERE duos.movie_id IS NULL