Get suppliers that doesn#39;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).
推荐答案
与其使用子选择,不如尝试使用基于集合的操作,例如 join
s 或 exists
.下面是针对您情况的一种选择,尽管有多种方法可以实现您的目标.哪个最好取决于您的数据:
Rather than using sub-selects, try to use set based operations like join
s 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
)
这篇关于获取不属于另一个表中某个类别的供应商的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:获取不属于另一个表中某个类别的供应商
基础教程推荐
- 如何根据该 XML 中的值更新 SQL 中的 XML 2021-01-01
- 二进制文件到 SQL 数据库 Apache Camel 2021-01-01
- 在多列上分布任意行 2021-01-01
- oracle区分大小写的原因? 2021-01-01
- 什么是 orradiag_<user>文件夹? 2022-01-01
- 如何在 SQL 中将 Float 转换为 Varchar 2021-01-01
- MySQL 中的类型:BigInt(20) 与 Int(20) 2021-01-01
- 表 './mysql/proc' 被标记为崩溃,应该修复 2022-01-01
- 在 MySQL 中:如何将表名作为存储过程和/或函数参数传递? 2021-01-01
- mysql选择动态行值作为列名,另一列作为值 2021-01-01