选择与此联接条件不匹配的行

发布于 2024-12-05 00:53:07 字数 542 浏览 2 评论 0原文

我在检查由 25 个表的脚本构建的搜索索引表 (asIndex) 时出错。

asIndex 和Evaluations 表的每个地址都有多行。

因此，此查询试图确保索引行的所有位置都有Evaluation = 'blue'，并且Evaluation 中至少有一行Evaluation 设置为'blue'。它可以工作，但会产生 40K 行。

SELECT 
    ev.`Street Name`, 
    ev.`Street Number`, 
    ev.Evaluation
FROM 
  `tblEvaluations` ev,
  `asIndex` asi
WHERE asi.`Evaluation` = 'blue'
AND asi.`StreetName` = ev.`Street Name`
AND asi.`StreetNumber` = ev.`Street Number`;

我需要做的是进行一个查询，列出在 asIndex 中具有蓝色评估值但在评估表中没有具有蓝色评估值的匹配地址的任何地址。

我如何选择否定匹配？

原文

I'm error checking a search index table (asIndex) that is built by a script from 25 tables.

Both the asIndex and Evaluations tables have multiple rows per address.

So this query was an attempt to be sure that everywhere an index row has Evaluation = 'blue' that there was at least one row in Evaluations where Evaluation is set to 'blue'.
It works but it produces 40K rows.

SELECT 
    ev.`Street Name`, 
    ev.`Street Number`, 
    ev.Evaluation
FROM 
  `tblEvaluations` ev,
  `asIndex` asi
WHERE asi.`Evaluation` = 'blue'
AND asi.`StreetName` = ev.`Street Name`
AND asi.`StreetNumber` = ev.`Street Number`;

What I need to do is make a query that will list any address which has a blue Evaluation in the asIndex but for which there is no matching address in Evaluations table with a blue Evaluation value.

How do I select that negative match?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淤浪 2024-12-12 00:53:07

您可以使用左连接。

SELECT 
    asi.`Street Name`, 
    asi.`Street Number`, 
    asi.Evaluation
FROM 
  `asIndex` asi ledt join
  `tblEvaluations` ev on asi.`StreetName` = ev.`Street Name`
      AND asi.`StreetNumber` = ev.`Street Number`
WHERE asi.`Evaluation` = 'blue'
   AND ev.`Street Name` is Null;

You could use a left join.

SELECT 
    asi.`Street Name`, 
    asi.`Street Number`, 
    asi.Evaluation
FROM 
  `asIndex` asi ledt join
  `tblEvaluations` ev on asi.`StreetName` = ev.`Street Name`
      AND asi.`StreetNumber` = ev.`Street Number`
WHERE asi.`Evaluation` = 'blue'
   AND ev.`Street Name` is Null;

回复收藏 0 原文

稳稳的幸福 2024-12-12 00:53:07

NOT EXISTS 也是最干净和标准的 SQL

SELECT 
    ev.`Street Name`, 
    ev.`Street Number`, 
    ev.Evaluation
FROM 
  `tblEvaluations` ev
WHERE 
  NOT EXISTS (SELECT * FROM   `asIndex` asi
           WHERE
    asi.`Evaluation` = 'blue'
    AND asi.`StreetName` = ev.`Street Name`
    AND asi.`StreetNumber` = ev.`Street Number
    );

编辑：埃文有一个观点（删除了他们的答案，这可能是你想要的）。这取决于您如何过滤蓝色。

NOT EXISTS is cleanest and standard SQL too

SELECT 
    ev.`Street Name`, 
    ev.`Street Number`, 
    ev.Evaluation
FROM 
  `tblEvaluations` ev
WHERE 
  NOT EXISTS (SELECT * FROM   `asIndex` asi
           WHERE
    asi.`Evaluation` = 'blue'
    AND asi.`StreetName` = ev.`Street Name`
    AND asi.`StreetNumber` = ev.`Street Number
    );

Edit: evan had a point (deleted their answer which may be what you want). It depends how you filter for blue.

回复收藏 0 原文

~没有更多了~