Access 2007 - 左连接查询返回 #Error 而不是 Null [英] Access 2007 - Left Join to a query returns #Error instead of Null

问题描述

类似于这个问题，但他们的查询问题从未完全解决:

Similar to this question, but their problem with the query was never fully solved:

#错误显示在多个 LEFT JOIN 语句中访问查询，当值应该为 NULL 时

当我在执行左联接时期望看到 Null 时，我收到 #Error，而联接的右侧没有相应的记录:

I get #Error when I'm expecting to see a Null when doing a left join where there is no corresponding record on the right hand side of the join:

Chain               CasesPerMonthPerStore   MonthOfFirstOrder
Naturally           2.3                     5/1/2011
Tom's Market        #Error
Livingstons         #Error
EverClear           3.1                     7/1/2012
Bob's Market        2.66                    5/1/2012
Andy's Exports      #Error
Jamestowns          0.89                    7/1/2012

如果我将数据复制到一个表中并左连接到这个表，它工作正常，所以我认为查询的语法有问题:

It works fine if I copy the data into a table and left join to this table, so I assume there is something wrong in the syntax of the query:

SELECT 
    MonthRange.Chain,
    MonthRange.CasesShipped/IIf(MonthsSinceFirstOrder.Months>DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1,
                                DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1,
                                MonthsSinceFirstOrder.Months)/NumStores.NumberOfStores AS CasesPerMonthPerStore,
    MonthsSinceFirstOrder.MonthOfFirstOrder
FROM 
    QueryDates, 
    (
        MonthRange 
        INNER JOIN 
        NumStores 
            ON MonthRange.Chain=NumStores.Chain
    ) 
    INNER JOIN 
    MonthsSinceFirstOrder 
        ON MonthRange.Chain=MonthsSinceFirstOrder.Chain;

此 SQL 返回正确的结果，它只是在左连接时返回#Errors 的行为.

This SQL returns the correct results, it's just its behaviour when Left Joining to it that is returning #Errors.

nb 中间奇怪的 Iif 语句检查自第一个订单以来的月数是否大于指定日期范围内的月数 - 所以如果日期范围有 6 个月并且第一个订单是在结束日期前 9 个月，它使用 6；如果第一个订单在结束日期前仅 4 个月，则使用 4.

nb the strange Iif statement in the middle checks to see if the number of months since the first order is greater than the number of months included in the specified date range - so if the date range has 6 months and the first order was 9 months before the end date, it uses 6; if the first order was only 4 months before the end date, it uses 4.

-- 编辑更新--

对，我把查询的元素一个一个地取出来，这是我能得到的最简单的方法，同时仍然重新创建左连接错误:

Right, I took elements of the query out one by one, and this is the simplest I can get whilst still recreating the left joining error:

SELECT 
    MonthRange.Chain, 
    DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1 AS CasesPerMonthPerStore
FROM 
    QueryDates, 
    MonthRange;

这就是我加入它的方式:

And this is how I'm left joining to it:

SELECT 
    Chains.Chain, 
    ErrorQuery.CasesPerMonthPerStore
FROM 
    Chains 
    LEFT JOIN 
    ErrorQuery 
        ON Chains.Chain=ErrorQuery.Chain;

这个 SQL 有什么地方不对吗?

Does anything in this SQL look wrong?

推荐答案

虽然查询应该根据连接类型返回 Null，但正如 Allen Browne 在他的文章中所述，错误:外连接表达式检索错误、

While the query should return Null based on the join type, as Allen Browne states in his article, Bug: Outer join expressions retrieved wrongly,

相反，它的行为就像 [JET 查询优化器] 在它返回来自较低级别查询的结果之后正在评估表达式."

"Instead, it behaves as if [the JET query optimizer] is evaluating the expression after it has returned the results from the lower-level query."

因此，您必须使用表达式选择计算字段，如果连接的右侧与左侧不匹配，则该表达式将评估为 Null.

Consequently, you must select the calculated field using an expression that will evaluate to Null if the right-side of the join doesn't match the left-side.

以您的精简代码为例:

SELECT 
Month.Chain,
DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1 AS CasesPerMonthPerStore
FROM
QueryDates,
MonthRange;

SELECT
Chains.Chain,
IIf(IsNull(ErrorQuery.Chain),Null,ErrorQuery.CasesPerMonthPerStore)
FROM
Chains
LEFT JOIN
ErrorQuery
ON Chains.Chain=ErrorQuery.Chain;

这篇关于Access 2007 - 左连接查询返回 #Error 而不是 Null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！