How can i make this MySQL query work?
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 st开发者_Python百科ruggeling for days now, can someone assist me ?
Thanks!
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)
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.
The answer to my question here may help you a bit (has some additional explanation that will clarify Roland's answer further).
精彩评论