不明确的外连接？

问题描述：

“的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;

感谢您的编辑...我不知道如何把它放在代码框。：D – Ben

我认为你应该给每个表和子查询别名 –

我已经阅读了几篇关于此的文章：http：//www.access-programmers.co.uk/forums/showthread.php？t = 137883。我曾尝试将此应用于我的查询，但无法成功地适应此方法。 – Ben

答

如果您创建一个包含LEFT JOIN和INNER JOIN的查询，访问可能无法确定哪个连接操作首先执行。由于结果根据左连接还是内连接先执行而不同，因此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

看起来很合理，不知道它是否正确。 :( –

它解决了这个问题！ – Ben

谢谢，你必须等待24小时才能接受你自己的答案 – Ben

不明确的外连接？

相关推荐