sql - 不明确的外部连接？

Question

我收到以下错误消息...

“无法执行 SQL 语句，因为它包含不明确的外部连接。要强制首先执行其中一个连接，请创建一个单独的查询来执行第一个连接，然后将该查询包含在您的 SQL 语句中。”

我该如何解决这个问题？？？如果我将“LEFT JOIN”替换为“INNER JOIN”，错误就会消失，但无法完成所需的操作。

这是我的代码:

SELECT route.productfam, 
       facility.location, 
       asmlines.line, 
       tableconsolidate2.sumofyr, 
       tableconsolidate2.sumofyr0, 
       tableconsolidate2.sumofyr1, 
       tableconsolidate2.sumofyr2, 
       tableconsolidate2.sumofyr3, 
       tableconsolidate2.sumofyr4, 
       tableconsolidate2.sumofyr5, 
       route.cycletime, 
       route.numperprod, 
       facilitylines.operationalhr, 
       [18months].[month 1], 
       [18months].[month 2], 
       [18months].[month 3], 
       [18months].[month 4], 
       [18months].[month 5], 
       [18months].[month 6], 
       [18months].[month 7], 
       [18months].[month 8], 
       [18months].[month 9], 
       [18months].[month 10], 
       [18months].[month 11], 
       [18months].[month 12], 
       [18months].[month 13], 
       [18months].[month 14], 
       [18months].[month 15], 
       [18months].[month 16], 
       [18months].[month 17], 
       [18months].[month 18] 
FROM   ((productfamily 
         INNER JOIN (facility 
                     INNER JOIN tableconsolidate2 
                             ON facility.location = 
                                tableconsolidate2.[build plant]) 
                 ON productfamily.productfamily = 
                    tableconsolidate2.[prod series]) 
        LEFT JOIN 18months 
               ON ( facility.location = [18months].location ) 
                  AND ( productfamily.productfamily = [18months].[item type] )) 
       INNER JOIN ((asmlines 
                    INNER JOIN facilitylines 
                            ON asmlines.line = facilitylines.line) 
                   INNER JOIN route 
                           ON asmlines.line = route.line) 
               ON ( productfamily.productfamily = route.productfam ) 
                  AND ( facility.location = facilitylines.facility ) 

GROUP BY route.productfam, facility.location, Asmlines.line, tableconsolidate2.SumOfyr, tableconsolidate2.SumOfyr0, tableconsolidate2.SumOfyr1, tableconsolidate2.SumOfyr2, tableconsolidate2.SumOfyr3, tableconsolidate2.SumOfyr4, tableconsolidate2.SumOfyr5, route.cycletime, route.numperprod, facilitylines.operationalhr, [18Months].[Month 1], [18Months].[Month 2], [18Months].[Month 3], [18Months].[Month 4], [18Months].[Month 5], [18Months].[Month 6], [18Months].[Month 7], [18Months].[Month 8], [18Months].[Month 9], [18Months].[Month 10], [18Months].[Month 11], [18Months].[Month 12], [18Months].[Month 13], [18Months].[Month 14], [18Months].[Month 15], [18Months].[Month 16], [18Months].[Month 17], [18Months].[Month 18], route.productfam

ORDER BY facility.location;

Answer 1

如果您创建包含 LEFT JOIN 和 INNER JOIN 的查询，Access 可能无法确定首先执行哪个联接操作。由于先执行左联接还是先执行内联接，结果会有所不同，因此 Access 会显示一条错误消息:

要更正此错误，您必须修改查询，以便清楚首先执行哪个连接。

因此，可以通过将其拆分为两个查询然后将它们加入其他查询来实现解决方案。

查询 1:

SELECT route.productfam, facility.location, Asmlines.line, [18Months].[Month 1], [18Months].[Month 2], [18Months].[Month 3], [18Months].[Month 4], [18Months].[Month 5], [18Months].[Month 6], [18Months].[Month 7], [18Months].[Month 8], [18Months].[Month 9], [18Months].[Month 10], [18Months].[Month 11], [18Months].[Month 12], [18Months].[Month 13], [18Months].[Month 14], [18Months].[Month 15], [18Months].[Month 16], [18Months].[Month 17], [18Months].[Month 18], route.cycletime, route.numperprod, facilitylines.operationalhr
FROM (facility INNER JOIN (ProductFamily INNER JOIN 18Months ON ProductFamily.productfamily = [18Months].[Item Type]) ON facility.location = [18Months].Location) INNER JOIN ((Asmlines INNER JOIN facilitylines ON Asmlines.line = facilitylines.line) INNER JOIN route ON Asmlines.line = route.line) ON (ProductFamily.productfamily = route.productfam) AND (facility.location = facilitylines.facility)
GROUP BY route.productfam, facility.location, Asmlines.line, [18Months].[Month 1], [18Months].[Month 2], [18Months].[Month 3], [18Months].[Month 4], [18Months].[Month 5], [18Months].[Month 6], [18Months].[Month 7], [18Months].[Month 8], [18Months].[Month 9], [18Months].[Month 10], [18Months].[Month 11], [18Months].[Month 12], [18Months].[Month 13], [18Months].[Month 14], [18Months].[Month 15], [18Months].[Month 16], [18Months].[Month 17], [18Months].[Month 18], route.cycletime, route.numperprod, facilitylines.operationalhr, route.productfam
ORDER BY facility.location;

查询 2:

SELECT route.productfam, facility.location, Asmlines.line, tableconsolidate2.SumOfyr, tableconsolidate2.SumOfyr0, tableconsolidate2.SumOfyr1, tableconsolidate2.SumOfyr2, tableconsolidate2.SumOfyr3, tableconsolidate2.SumOfyr4, tableconsolidate2.SumOfyr5, route.cycletime, route.numperprod, facilitylines.operationalhr
FROM (ProductFamily INNER JOIN (facility INNER JOIN tableconsolidate2 ON facility.location = tableconsolidate2.[Build Plant]) ON ProductFamily.productfamily = tableconsolidate2.[Prod Series]) INNER JOIN ((Asmlines INNER JOIN facilitylines ON Asmlines.line = facilitylines.line) INNER JOIN route ON Asmlines.line = route.line) ON (ProductFamily.productfamily = route.productfam) AND (facility.location = facilitylines.facility)
GROUP BY route.productfam, facility.location, Asmlines.line, tableconsolidate2.SumOfyr, tableconsolidate2.SumOfyr0, tableconsolidate2.SumOfyr1, tableconsolidate2.SumOfyr2, tableconsolidate2.SumOfyr3, tableconsolidate2.SumOfyr4, tableconsolidate2.SumOfyr5, route.cycletime, route.numperprod, facilitylines.operationalhr, route.productfam
ORDER BY facility.location;

查询 3:

Query 1 LEFT JOIN Query 2