一个表中的SQL行不在另一个多个字段中

I am modelling an on-line purchasing and rating service and have two tables:

我正在建立一个在线购买和评级服务,并有两个表:

Bought (UserID, ItemID)

Rating (UserID, ItemID, Rating)

I want to return those who are in the first table but not in the second one, i.e. those who have bought and item but not rated it.

我想要归还那些在第一张桌子而不在第二张桌子里的人,即那些购买过物品但没有评价过的人。

so far ive got:

到目前为止我已经:

SELECT 
user,
item 
FROM Buys 
where item NOT IN (SELECT Item FROM Rates);

But that will only return users who have bought items which have no ratings at all. As soon as somebody else rates the item it is no longer returned.

但这只会让那些购买了根本没有评级的商品的用户返回。一旦其他人评价该项目,它就不再返回。

How do you specify:

你如何指定:

SELECT 
user,
item 
FROM Buys 
where item **& user** NOT IN (SELECT Item **& User** FROM Rates);

3 个解决方案

#1

Try this:

SELECT *
FROM Bought B
WHERE NOT EXISTS 
(
   SELECT *
   FROM Rating R
   WHERE B.UserID = R.UserID AND B.ItemID = R.ItemID
)

#2

You need to filter UserID instead of ItemID. Also you don't need to filter both UserID and ItemID to find the user's who didn't rate any product

您需要过滤UserID而不是ItemID。此外,您不需要同时过滤UserID和ItemID来查找未对任何产品评分的用户

SELECT user,item 
FROM Buys B 
where not exists
(
SELECT 1 
FROM Rates R 
where B.UserID =R.UserID 
)

#3

For sql-server use EXCEPT

对于sql-server,使用EXCEPT

SELECT 
  user, item 
FROM 
  Buys
EXCEPT
SELECT 
  user, item 
FROM 
  Rates

For mysql use MINUS

对于mysql使用MINUS

SELECT 
  user, item 
FROM 
  Buys
MINUS
SELECT 
  user, item 
FROM 
  Rates

#1