How can I filter this MySQL query only to include elements not rated by a particular user?

Question

Given this sample data:

item
----
item_id  item_name  item_added
1        Apple      <date_time>
2        Banana     <date_time>

user
----
user_id  user_name
1        Alice
2        Bob
3        Carol

rating
------
rating_id  item_id  user_id  rating_value
1          1        1        3
2          1        2        4
3          1        3        5
4          2        1        5
5          2        2        2

I have this query to find out which items are missing only one rating:

SELECT item.*,
       COUNT(rating_value) AS rating_count
  FROM item
LEFT JOIN rating ON item.item_id = rating.item_id
 GROUP BY item_id
   HAVING rating_count = 2
 ORDER BY item_name

How can I modify this query to display items that only miss Carol's rating? The string rating_count is not significant. The only significant columns are item_id and item_name.

Thank.

Answer 1

This will lead to the absence of Carol rating in the elements (user_id = 3, which should be passed as a parameter):

SELECT item.*, COUNT(rating.rating_value) AS rating_count 
 FROM item INNER JOIN rating ON item.item_id = rating.item_id AND 
      (3 NOT IN (SELECT r.user_id FROM rating AS r 
          WHERE r.item_id = rating.item_id))
 GROUP BY item_id
 HAVING rating_count = 2   /* (2 = number of users - 1) */
 ORDER BY item_name;

item movie, , , , .

Answer 2

Select ...
From item 
    Left Join rating 
        On rating.item_id = item.item_id
            And rating.user_id = 3
Where rating.rating_id Is Null
Order By item.item_name 

, :

Select ...
From item 
    Left Join rating 
        On rating.item_id = item.item_id
Where Not Exists    (
                    Select 1
                    From rating As R1
                    Where R1.item_id = item.item_id
                        And R1.user_id = 3
                    )
Order By item.item_name