MS Access左联接无法正常工作 [英] MS Access Left Join not working correctly

问题描述

我正在尝试在MS Access 2013中编写查询，并且左联接无法正常工作.它的行为就像普通的联接.

I am trying to write a query in MS Access 2013 and the left join is not working correctly. It is acting like a normal join.

这就是我想要做的.

我的第一个表[All Category Types]有一个列[Category Types].然后，我试图将其连接到具有两个聚合字段的查询中.虚拟表[Average by Category Type]首先按所有者分组，然后按[Category Type]分组.接下来是求和字段[CountOfIncident: Number].

My first table [All Category Types] has one column [Category Types]. I am then trying to left join that to a query that has two aggregate fields in it. Virtual Table [Average by Category Type] is first grouped by Owner, and then by [Category Type]. Next is a sum field [CountOfIncident: Number].

作为结果，我想要的是表[All Category Types]中的每个项目，然后是正确的[CountOfIncident: Number] where Owner == "France".这不能作为左联接.它仅向我显示[All Category Types]中的值，这些值在[Average by Category Type]中具有匹配的记录.

What I want as a result is every item in table [All Category Types] and then the correct [CountOfIncident: Number] where Owner == "France". This is not working as a left join. It is only showing me the values in [All Category Types] that have a matching record in [Average by Category Type].

如果我从此表中删除所有者"，并且仅按"[Category Type]"分组，则效果很好.关于group by子句中有多个字段的问题，这些字段不允许查询的左联接正常工作?

If I remove Owner from this table, and only group by [Category Type], it works just fine. Is there something about having more than one field in the group by clause that does not allow a left join on a query to work correctly?

SELECT [All Category Types].[Category Type], 
       [Average by Category Type].[CountOfIncident: Number]
FROM [All Category Types] 
LEFT JOIN [Average by Category Type] 
ON [All Category Types].[Category Type] = [Average by Category Type].[Category Type]
WHERE ((([Average by Category Type].Owner)="France"));

谢谢.

推荐答案

[Average by Category Type].Owner = "France"仅适用于内部联接记录.对于外部联接的记录，[Average by Category Type].Owner为空.

[Average by Category Type].Owner = "France" can only be true for inner joined records. For outer joined records [Average by Category Type].Owner is null.

因此，您的WHERE子句将您的外部联接变成内部联接.将条件移到ON子句:

So your WHERE clause turns your outer join into an inner join. Move the condition to the ON clause:

SELECT 
  [All Category Types].[Category Type], 
  [Average by Category Type].[CountOfIncident: Number]
FROM [All Category Types] 
LEFT JOIN [Average by Category Type] 
  ON ([Average by Category Type].[Category Type] = [All Category Types].[Category Type]
  AND [Average by Category Type].Owner = "France");

更新:与其他DBMS不同，MS Access需要在ON子句中加上括号:JOIN tablename ON (...)而不是JOIN tablename ON ....

UPDATE: Unlike other DBMS MS Access needs parentheses for the ON clause: JOIN tablename ON (...) rather than JOIN tablename ON ....

这篇关于MS Access左联接无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！