选择所有客户，除非他们有其他产品 - SQL [英] Select all customers except if they have another product - SQL

问题描述

我有两张桌子.一个包含客户，另一个包含他们购买的产品:

I have two tables. One consists of customers and another consists of products they have purchased:

表客户

CustID, Name
  1,     Tom 
  2,     Lisa 
  3,     Fred

餐桌产品

CustID, Item
  1,     Toaster
  1,     Breadbox
  2,     Toaster
  3,     Toaster

我希望获得所有购买烤面包机的客户，除非他们还购买了面包盒.

I would like to get all the customers that bought a Toaster, unless they also bought a breadbox.

所以我尝试了以下方法:

So I have tried the following:

SELECT * FROM Customer
JOIN Product
    ON Customer.CustID=Product.CustID
WHERE Product in
    (SELECT 
        Item
    FROM Product
    WHERE (Item = 'Toaster' AND Item != 'Breadbox'));

还有:

SELECT * FROM Customer
INNER Join Product
ON Customer.CustID=PRODUCT.CustID
WHERE Product.Item = 'Toaster'
AND Product.Item NOT IN ('Breadbox');

但两者都给出了相同的结果，其中包括已经拥有面包箱的汤姆.

But both gives the same result, which includes Tom, that does already own a breadbox.

我如何确保只有拥有烤面包机但不拥有面包盒的客户才会被列出?

How can I make sure that only the customers that own a toaster, but do not own a breadbox gets listed?

推荐答案

SELECT distinct * FROM Customer
 LEFT JOIN Product ON Customer.CustID=Product.CustID
WHERE Item = 'Toaster'
AND Customer.CustID NOT IN (
Select CustID FROM Product Where Item = 'Breadbox'
)

这篇关于选择所有客户，除非他们有其他产品 - SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！