使用SQL查找不匹配的记录 [英] Finding unmatched records with SQL

问题描述

我正在尝试编写查询以查找在另一个表中没有匹配记录的记录.

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

(状态/产品/分销商共同构成表1的键.状态/产品是表2的键)

(State/Product/Distributor together form the key for Table1. State/Product is the key for Table2)

我想找到所有未使用发行商X的州/产品/版本组合.(因此，本示例中的结果是CA-P1-1.0和VA-P2-1.2.)

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')

这应该符合ANSI.

这篇关于使用SQL查找不匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！