How to search when joining 3 tables but exclude result for one of them?

I've been tying for hours now to get a particular result and haven't found any answer on the w开发者_C百科eb - and as I'm not an SQL expert at all, I'm asking a question here.

I have 3 tables: user (id, name...), cars (id, type, color, engine power...) and an intermediary table to save all the scores users gave to the car: scores (id, user_id, car_id, score).

I'm trying to find a query that could return for one particular user, all the cars that he hasn't rated yet. I've tried the following but it returns null:

$q=mysql_query("SELECT * FROM cars LEFT OUTER JOIN scores ON cars.id = scores.car_id WHERE scores.user_id != ('".$userId."')");

Does someone have a clue?

SELECT
  *
FROM
  cars
WHERE
  NOT EXISTS (SELECT 1 FROM scores WHERE car_id = cars.id AND user_id = ?)

where ? is the ID of that particular user.

A composite index in scores over (car_id, user_id) is useful here.

You can use your code with small modification:

SELECT * FROM cars 
LEFT OUTER JOIN scores ON cars.id = scores.car_id and scores.user_id=".$userId."
WHERE scores.id IS NULL

SELECT * FROM
car c
WHERE c.id NOT IN (
    SELECT s.car_id
    FROM score s, user u
    WHERE u.id = s.user_id
        AND u.id = ?
)