模棱两可的外部联接? [英] Ambiguous Outer Joins?
本文介绍了模棱两可的外部联接?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我收到以下错误消息...
由于包含模糊的外部联接，因此无法执行该SQL语句.要强制其中一个联接首先执行，请创建一个单独的查询来执行第一个联接，然后将该查询包括在您的SQL语句中." /p>
该如何解决呢???如果我将"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;
解决方案
如果创建的查询包含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
I am getting the following error message...
"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."
HOW CAN I FIX THIS??? If i replace the "LEFT JOIN" with "INNER JOIN" the bug goes away but the desired operation is not accomplished.
Here is my code:
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;
解决方案
If you create a query that contains a LEFT JOIN and an INNER JOIN, Access may not be able to determine which join operation to perform first. Because the results are different depending on whether the left join or the inner join is performed first, Access displays an error message:
To correct this error, you must modify the query so that it is clear which join to perform first.
Consequently solution can be achieved by splitting this into two queries and then joining them in and additional query.
Query 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;
Query 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;
Query 3:
Query 1 LEFT JOIN Query 2
这篇关于模棱两可的外部联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
