SQLite Join – How to Join Three Tables Efficiently

join;sqlite

I have a question

Find the titles of all movies not reviewed by Chris Jackson

Where the relations are 3
movie(mID, title, year, director),
reviewer(rID, name),
rating(rID, mID, stars, ratingDate)

I have tried this query so far. Any idea of how to improve it or what am I doing wrong?

SELECT DISTINCT M.TITLE
FROM MOVIE AS M
JOIN RATING AS R ON R.mID = M.mID
WHERE R.rID NOT IN (SELECT Rating.rID FROM Rating JOIN Reviewer ON Reviewer.rID = Rating.rID WHERE Reviewer.name = 'Chris Jackson');

Best Answer

Your query is too complex; the join between movie and rating is not necessary because the movie ID is already directly available from the subquery.

Anyway, using subqueries instead of joins usually is easer:

First, these are all reviews by anyone named Chris Jackson:

SELECT *
FROM rating
WHERE rID IN (SELECT rID
              FROM reviewer
              WHERE name = 'Chris Jackson')

(If you know that there is only one Chris Jackson, use WHERE rID = (SELECT ... instead of WHERE rID IN (SELECT ....)

Now you want all movies that do not have such a review:

SELECT title
FROM movie
WHERE mID NOT IN (SELECT mID
                  FROM rating
                  WHERE rID IN (SELECT rID
                                FROM reviewer
                                WHERE name = 'Chris Jackson'))

It would be possible to combine the two inner subqueries with a join:

SELECT title
FROM movie
WHERE mID NOT IN (SELECT rating.mID
                  FROM rating
                  JOIN reviewer USING (rID)
                  WHERE reviewer.name = 'Chris Jackson')
Related Question