选择与此联接条件不匹配的行
我在检查由 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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您可以使用左连接。
You could use a left join.
NOT EXISTS 也是最干净和标准的 SQL
编辑:埃文有一个观点(删除了他们的答案,这可能是你想要的)。这取决于您如何过滤蓝色。
NOT EXISTS is cleanest and standard SQL too
Edit: evan had a point (deleted their answer which may be what you want). It depends how you filter for blue.