SQL查询以一个值为基础排除项目 [英] SQL query to exclude items on the basis of one value

问题描述

我正在从一个表中提取项目列表，因为它们包含在另一个表中，如下所示:

I'm pulling a list of items from one table, on the basis of their being included in another table, like this:

select fruit.id, fruit.name from fruit, fruit_rating where fruit_rating.fruit_id=fruit.id group by fruit.name;

这很好用-基本上会列出某人已评分的所有水果的列表.但是现在，我想排除所有已由一个特定用户评级的水果，所以我尝试了以下方法:

That works fine -- it basically produces a list of all the fruit that has been rated by someone. But now, I want to exclude all fruit that has been rated by one specific user, so I tried this:

select fruit.id, fruit.name from fruit, fruit_rating where fruit_rating.fruit_id=fruit.id and fruit_rating.user_id != 10 group by fruit.name;

可以，但是不太正确.它显示了除10以外其他人已评级的所有水果，但是如果用户1和10都对同一水果进行了评级，则仍显示该水果.谁能告诉我如何构造一个查询，仅显示未由用户10评级的水果，而不管其他人对其进行了评级吗?

That's ok, but not quite right. It shows all the fruit that have been rated by people other than 10, but if users 1 and 10 have both rated the same fruit, it still shows that one. Can anyone tell me how to construct a query that shows only the fruit that have NOT Been rated by user 10, regardless of who else has rated them?

推荐答案

... WHERE fruit_rating.fruit_id=fruit.id 
      and fruit.id not in 
          (select fruit_rating.fruit_id 
             from fruit_rating 
            where fruit_rating.user_id = 10)

这篇关于SQL查询以一个值为基础排除项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！