How do I filter this MySQL query to only include items not rated by a specific user?
Given this sample dataset:
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 figure 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 show items that are only missing Carol's rating? The rating_count row is not essential. The only essential columns are item_id and item_name.
Thank yo开发者_高级运维u.
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
If you still wanted rating data, here's another form:
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
This will output the items only missing Carol's rating (user_id = 3 that needs to 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;
You have to replace all item
by movie
but your query mixes item and movie and the table was item so I did the query with item and I only saw the movie in the query too late.
精彩评论