MySQL look for missing ratings

Question

Hopefully I can ask this without being confusing. I am a photographer and I am having some of our clients rate pictures that we have taken. We have hundreds of pictures in our portfolio that they may be rating. What I want to do is ask our clients to rate pictures again, but only show them the pictures they haven't yet rated. I currently have three tables: one that stores the actual ratings, one that stores the pictures (or location of each picture), and one that stores the information about the rater. I'm using codeigniter for my db management, if that helps at all. What I have so far is this:

"SELECT * FROM ratings LEFT JOIN portfolio ON ratings.portfolioid = portfolio.portfolioid"

This will give me a row for each rating, but won't show me where a rating is missing for a picture. Thanks in advance!

Answer 1

SELECT * FROM portfolio WHERE NOT EXISTS ( SELECT * FROM ratings where ratings.portfolioid = portfolio.portfolioid )

SELECT * FROM portfolio LEFT JOIN ratings ON portfolio.portfolioid = ratings.portfolioid WHERE ratings.portfolioid IS NULL

Should both give you the ones with no rating.

Answer 2

I can't test it right now, but would something like this do the job:

SELECT * FROM portfolio LEFT JOIN (
  SELECT portfolioid FROM rating WHERE email = "[email protected]"
) AS rated USING (portfolioid)
WHERE rated.portfolioid IS NULL

Answer 3

@ChrisV - You can avoid the subselect with this version:

SELECT * FROM portfolio p 
LEFT OUTER JOIN rating r ON r.portfolioid = p.portfolioid AND r.email = '[email protected]'
WHERE r.portfolioid IS NULL

That's the beauty of OUTER JOIN with extra conditions. The extra condition gets applied before the join. It will only join portfolios to ratings that match the extra condition (AND r.email = ...), so if a rating doesn't exist, it means that '[email protected]' hasn't rated that portfolio.