1

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');

  • What results are you getting? – RLF May 21 '14 at 21:03
  • I'd do the join all together, and just do the not in for the name only, but yes what results are you getting? – Eric Higgins May 21 '14 at 22:51
2

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')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.