How can i make this MySQL query work?

Question

I have a table called 'bandsplusrating' and a table called 'ratings' The table 'bandsplusrating' is filled with bands, the table 'ratings' starts empty.

Both tables have field called 'naam' with which they can be joined. Table 'ratings' has a field called 'fullname'

Multiple Ratings per band (bandsplusrating) will be stored in table 'ratings'

Now i want to display all records from 'bandsplusrating' which havent been rated by a given user (ratings.fullname)

SELECT b.naam, b.stijl
FROM bandsplusrating b
LEFT JOIN ratings r ON b.naam = r.naam
WHERE r.fullname NOT LIKE 'Obama'

This query will still display a record when an other user has rated it.

Im am struggeling for days now, can someone assist me ?

Thanks!

Answer 1

SELECT    b.naam, b.stijl
FROM      bandsplusrating b
LEFT JOIN ratings r 
ON        b.naam  = r.naam
AND       'Obama' = r.fullname
WHERE     r.naam IS NULL

The trick is, the LEFT JOIN is evaluated before the WHERE. So by first letting the join roll out, any rows from bandplusrating that do not match anything in ratings will yield a NULL-record for ratings (a ratings record with only NULL values). Then, you can filter on that using the WHERE and by demanding that some known mandatory column from ratings IS NULL, you're effectively saying: only those that didn't match.

EDIT:

I just saw OMG Ponies' thorough reply, and decided to add yet another method to do it, which is just a slight variation of the LEFT JOIN solution:

SELECT    b.naam, b.stijl
FROM      bandsplusrating b
LEFT JOIN ratings r 
ON        b.naam  = r.naam
AND       'Obama' = r.fullname
GROUP BY  b.naam, b.stijl
HAVING    COUNT(r.naam) = 0

(Personally I think this one is less good that the prior one)

Answer 2

The answer to my question here may help you a bit (has some additional explanation that will clarify Roland's answer further).

Answer 3

Using NOT IN:

---

SELECT b.naam,
       b.stigl
  FROM BANDPLUSRATING b
 WHERE b.naam NOT IN (SELECT r.naam
                        FROM RATINGS r
                       WHERE r.fullname LIKE 'Obama')

Using NOT EXISTS:

---

SELECT b.naam,
       b.stigl
  FROM BANDPLUSRATING b
 WHERE NOT EXISTS(SELECT NULL
                    FROM RATINGS r
                   WHERE r.naam = b.naam
                     AND r.fullname LIKE 'Obama')

Using LEFT JOIN/IS NULL:

---

   SELECT b.naam,
          b.stigl
     FROM BANDPLUSRATING b
LEFT JOIN RATINGS r ON r.naam = b.naam
                   AND r.fullname LIKE 'Obama'
    WHERE r.naam IS NULL

Performance

---

Of the three options, the NOT IN and LEFT JOIN/IS NULL perform equally. NOT EXISTS doesn't perform as well.