I'm doing Q4 (Find the titles of all movies not reviewed by Chris Jackson. ) from SQL Movie-Rating Query Exercises Extras and I don't know why this code doesn't work:
SELECT DISTINCT movie.title
FROM movie
INNER JOIN rating ON movie.mid = rating.mID
INNER JOIN reviewer ON rating.rid = reviewer.rid
WHERE rating.mid NOT IN (SELECT rating.mid FROM rating WHERE rating.rid = (SELECT reviewer.rid FROM reviewer WHERE reviewer.name = 'Chris Jackson') )
Output:
title
Gone with the Wind
Snow White
Avatar
This output doesn't include movies that ARE in movie table but ARE NOT in rating table. So I suspect maybe this has something to do with JOIN clause.
TABLES:
Movie
mID title year director
101 Gone with the Wind 1939 Victor Fleming
102 Star Wars 1977 George Lucas
103 The Sound of Music 1965 Robert Wise
104 E.T. 1982 Steven Spielberg
105 Titanic 1997 James Cameron
106 Snow White 1937 <null>
107 Avatar 2009 James Cameron
108 Raiders of the Lost Ark 1981 Steven Spielberg
Reviewer
rID name
201 Sarah Martinez
202 Daniel Lewis
203 Brittany Harris
204 Mike Anderson
205 Chris Jackson
206 Elizabeth Thomas
207 James Cameron
208 Ashley White
Rating
rID mID stars ratingDate
201 101 2 2011-01-22
201 101 4 2011-01-27
202 106 4 <null>
203 103 2 2011-01-20
203 108 4 2011-01-12
203 108 2 2011-01-30
204 101 3 2011-01-09
205 103 3 2011-01-27
205 104 2 2011-01-22
205 108 4 <null>
206 107 3 2011-01-15
206 106 5 2011-01-19
207 107 5 2011-01-20
208 104 3 2011-01-02
Firstly you must use LEFT JOIN and then then use GROUP BY movie.mid, movie.title and put the condition in the HAVING clause:
SELECT movie.title
FROM movie
LEFT JOIN rating ON movie.mid = rating.mID
LEFT JOIN reviewer ON rating.rid = reviewer.rid
GROUP BY movie.mid, movie.title
HAVING SUM(CASE WHEN reviewer.name = 'Chris Jackson' THEN 1 ELSE 0 END) = 0
See the demo.
Results:
> | title |
> | :----------------- |
> | Avatar |
> | Gone with the Wind |
> | Snow White |
> | Star Wars |
> | Titanic |