CODEWITHSUNDEEP
sqlpostgresql
The Lion King
The Lion King

Reputation: 19

How to list all movies staring ​Jeff Goldblum but not ​Bruce Willis'?

My model has 3 tables: ACTORS, MOVIES and MOVIE_CAST, which represents the relationship between ACTORS and MOVIES.

Table: ACTORS

Table: Movies

Table: MOVIE_CAST

I need to list the title, release year, genre and director of all movies staring ​'Jeff Goldblum' but NOT ​'Bruce Willis'​.

I've tried the query below but it's not working. It returns me all the movies that ​Jeff Goldblum starred, including the ones he did with Bruce Willis.

SELECT A.NAME, M.TITLE, M.RELEASE_YEAR, M.GENRE, M.DIRECTOR
FROM ACTORS A
    JOIN MOVIE_CAST MC ON A.NAME = MC.ACTOR AND A.BIRTH_YEAR = MC.BIRTH_YEAR
    JOIN MOVIES M ON M.TITLE = MC.MOVIE AND M.RELEASE_YEAR = MC.MOVIE_RELEASE_YEAR
WHERE A.NAME = 'Jeff Goldblum' AND A.NAME != 'Bruce Willis'

How can I write the query to return me the movies that Jeff Goldblum starred alone or with other actors, except Bruce Willis?

Upvotes: 0

Views: 171

Answers (3)

The Lion King
The Lion King

Reputation: 19

I've just discovered the EXCEPT statement (which is the MINUS statement depending on the database used).

So my query became:

SELECT M.TITLE, M.RELEASE_YEAR, M.GENRE, M.DIRECTOR
FROM   ACTORS A
    JOIN   MOVIE_CAST MC ON A.NAME = MC.ACTOR AND A.BIRTH_YEAR = MC.BIRTH_YEAR
    JOIN   MOVIES M ON M.TITLE = MC.MOVIE AND M.RELEASE_YEAR = MC.MOVIE_RELEASE_YEAR
WHERE  A.NAME = 'Jeff Goldblum' 

EXCEPT

SELECT M.TITLE, M.RELEASE_YEAR, M.GENRE, M.DIRECTOR
FROM   ACTORS A
    JOIN   MOVIE_CAST MC ON A.NAME = MC.ACTOR AND A.BIRTH_YEAR = MC.BIRTH_YEAR
    JOIN   MOVIES M ON M.TITLE = MC.MOVIE AND M.RELEASE_YEAR = MC.MOVIE_RELEASE_YEAR
WHERE  A.NAME = 'Bruce Willis'

And it worked!

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

One option would be to aggregate by movie (and its metadata) and then assert that Jeff Goldblum be present and Bruce Willis not be present:

SELECT M.TITLE, M.RELEASE_YEAR, M.GENRE, M.DIRECTOR
FROM ACTORS A
INNER JOIN MOVIE_CAST MC ON A.NAME = MC.ACTOR AND A.BIRTH_YEAR = MC.BIRTH_YEAR
INNER JOIN MOVIES M ON M.TITLE = MC.MOVIE AND M.RELEASE_YEAR = MC.MOVIE_RELEASE_YEAR
GROUP BY M.TITLE, M.RELEASE_YEAR, M.GENRE, M.DIRECTOR
HAVING
    COUNT(CASE WHEN A.NAME = 'Jeff Goldblum' THEN 1 END) > 0 AND  -- Goldblum appears
    COUNT(CASE WHEN A.NAME = 'Bruce Willis' THEN 1 END) = 0;      -- but not Willis

Upvotes: 1

Mureinik
Mureinik

Reputation: 311143

You could use a not exists condition to filter out the movies staring Bruce Willis:

SELECT A.NAME, M.TITLE, M.RELEASE_YEAR, M.GENRE, M.DIRECTOR
FROM   ACTORS A
JOIN   MOVIE_CAST MC ON A.NAME = MC.ACTOR AND A.BIRTH_YEAR = MC.BIRTH_YEAR
JOIN   MOVIES M ON M.TITLE = MC.MOVIE AND M.RELEASE_YEAR = MC.MOVIE_RELEASE_YEAR
WHERE  A.NAME = 'Jeff Goldblum' AND
       NOT EXISTS (SELECT *
                   FROM   ACTORS b
                   WHERE  b.NAME = MC.ACTOR AND 
                          b.BIRTH_YEAR = MC.BIRTH_YEAR AND
                          b.NAME = 'Bruce Willis')

Upvotes: 0

Related Questions