使用 SQL 查找不匹配的记录 [英] Finding unmatched records with SQL
问题描述
我正在尝试编写查询以查找在另一个表中没有匹配记录的记录.
例如,我有两个表,其结构如下所示:
<前>表格1状态 |产品 |经销商 |其他领域CA |P1 |一个 |xxx或 |P1 |一个 |xxx或 |P1 |乙 |xxx或 |P1 |X |xxxWA |P1 |X |xxx弗吉尼亚州 |P2 |一个 |xxx表2状态 |产品 |版本 |其他领域CA |P1 |1.0 |xxx或 |P1 |1.5 |xxxWA |P1 |1.0 |xxx弗吉尼亚州 |P2 |1.2 |xxx(State/Product/Distributor 共同构成 Table1 的 key.State/Product 是 Table2 的 key)
我想找到所有未使用分销商 X 的状态/产品/版本组合.(因此本示例中的结果是 CA-P1-1.0 和 VA-P2-1.2.)
对查询有什么建议吗?
SELECT*从表2 T2在哪里不存在(选择 *从表1 T1在哪里T1.State = T2.State ANDT1.Product = T2.Product ANDT1.Distributor = 'X')
这应该符合 ANSI.
I'm trying write a query to find records which don't have a matching record in another table.
For example, I have a two tables whose structures looks something like this:
Table1 State | Product | Distributor | other fields CA | P1 | A | xxxx OR | P1 | A | xxxx OR | P1 | B | xxxx OR | P1 | X | xxxx WA | P1 | X | xxxx VA | P2 | A | xxxx Table2 State | Product | Version | other fields CA | P1 | 1.0 | xxxx OR | P1 | 1.5 | xxxx WA | P1 | 1.0 | xxxx VA | P2 | 1.2 | xxxx
(State/Product/Distributor together form the key for Table1. State/Product is the key for Table2)
I want to find all the State/Product/Version combinations which are Not using distributor X. (So the result in this example is CA-P1-1.0, and VA-P2-1.2.)
Any suggestions on a query to do this?
SELECT
*
FROM
Table2 T2
WHERE
NOT EXISTS (SELECT *
FROM
Table1 T1
WHERE
T1.State = T2.State AND
T1.Product = T2.Product AND
T1.Distributor = 'X')
This should be ANSI compliant.
这篇关于使用 SQL 查找不匹配的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!