获取不属于另一个表中某个类别的供应商 [英] Get suppliers that doesn't belong to a category in another table

问题描述

我正在寻找一个查询，我需要在其中显示供应商表中没有类别 1 (Products.CategoryID = 1) 产品的所有供应商.每当我运行它时，它总是会出错.

I'm looking for a query where I need to show all the Suppliers from the Suppliers table that doesn't have products from category 1 (Products.CategoryID = 1). Whenever I run it it always gives an error.

Select SupplierID From Suppliers su
where SupplierID NOT IN
(select distinct SupplierID from Products 
where SupplierID in 
(select SupplierID from Products where CategoryID=1)

附带问题:如果供应商的产品来自 cat，我如何获得这些结果.6 ?(所以没有来自 cat1 但确实来自 cat6).

Side question: How do I get these results but with suppliers that has products from cat. 6 ? (So none from cat1 but does have from cat6).

推荐答案

与其使用子选择，不如尝试使用基于集合的操作，例如 joins 或 exists.下面是针对您情况的一种选择，尽管有多种方法可以实现您的目标.哪个最好取决于您的数据:

Rather than using sub-selects, try to use set based operations like joins or exists. One option for your situation is below, though there are several ways to achieve what you are looking to do. Which one is best will depend on your data:

select su.SupplierID
From Suppliers as su
where not exists(select null -- The only check here is for a record, so you can select any value and it won't change the functionality
                 from Products as p
                 where su.SupplierID = p.SupplierID
                   and p.CategoryID = 1
                )

这篇关于获取不属于另一个表中某个类别的供应商的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！