为什么LEFT JOIN导致“无法绑定多部分标识符"? INNER JOIN没有时出现错误? [英] Why is LEFT JOIN causing a "multi-part identifier can't be bound" error when INNER JOIN does not?

问题描述

尝试在Access 2010中运行以下SQL查询时，出现错误无法绑定多部分标识符'MS2.ExtraValueID'".

I'm getting the error "The multi-part identifier 'MS2.ExtraValueID' cannot be bound" when trying to run the following SQL query in Access 2010.

SELECT [Contact Details].ContactID
FROM ([Contact Details] LEFT JOIN [Extra Fields - Newsletters & Bulletins] ON [Contact Details].ContactID = [Extra Fields - Newsletters & Bulletins].ContactID) LEFT JOIN [Extra Fields - Number of Employees] ON [Extra Fields - Newsletters & Bulletins].ContactID = [Extra Fields - Number of Employees].ContactID;

ExtraValueID出现在两个"Extra Value ..."查询的WHERE子句中，但未输出.

ExtraValueID appears in the WHERE clause of both of the "Extra Value..." queries, but isn't outputted.

查找​​此问题从几年前开始，它表明这可能是由于打字错误造成的.但是，我看不到上面的SQL有什么问题，这表明组成这个更大的查询的单个查询之一是错误的.

Looking up this question from a few years back, it suggests this is probably due to a typo. However, I can't see anything wrong with the above SQL, which would suggest one of the individual queries which make up this bigger query is wrong.

但是，每个查询都可以单独运行，并且，如果我仅尝试将其中一个Extra Fields表联接到它，则可以正常运行.我以为&"号可能会引起问题，但是用"and"代替它们，或者完全删除它们并没有什么区别.

However, each of the queries individually runs fine, and if I only try to join one of the Extra Fields tables to it, it works fine. I thought the ampersands might be causing an issue, but replacing them with "and", or deleting them altogether makes no difference.

如果我将LEFT JOIN替换为INNER JOIN，它会返回结果，但不会返回我想要的结果，因为我应该从Contact Details中返回所有结果，看看两者是否有匹配的记录是否有额外字段"表.

If I replace the LEFT JOINs with INNER JOINs, it works inasmuch as it returns results, but not the results I want, because I should return all the results from Contact Details whether there are matching records in the two Extra Fields tables or not.

SELECT [Contact Details].ContactID, [Contact Details].[Client Code], [Extra Fields - Newsletters & Bulletins].Newsletters, [Extra Fields - Number of Employees].[Number of Employees]
FROM ([Contact Details] INNER JOIN [Extra Fields - Newsletters & Bulletins] ON [Contact Details].ContactID = [Extra Fields - Newsletters & Bulletins].ContactID) INNER JOIN [Extra Fields - Number of Employees] ON [Extra Fields - Newsletters & Bulletins].ContactID = [Extra Fields - Number of Employees].ContactID;

推荐答案

解决了它-Access引起了发脾气，因为即使INNER联接位于组成部分中，我也混用了INNER和LEFT联接构成较大查询的查询，应该刚刚输出结果.通过替换两个Extra Fields查询中的所有INNER联接，该查询花费的时间稍长一些，但可以正确运行.

Solved it - Access was throwing a tantrum because I'd mixed INNER and LEFT joins, even though the INNER joins were in the constituent queries that made up the larger query and should have just been outputting results. By replacing all of the INNER joins in the two Extra Fields queries, the query takes a little longer to run, but runs correctly.

这篇关于为什么LEFT JOIN导致“无法绑定多部分标识符"? INNER JOIN没有时出现错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！