odbc调用失败[microsoft] [sql server native client 11.0] [sql server]多部分标识符盘绕不绑定

问题描述：

我有两个查询，它们使用通过ODBC数据库链接的表，两个查询都非常简单并且可以单独工作.

I have two queries, which use tables linked using an ODBC database, both are simple enough and individually work fine.

查询#1:

SELECT 
    People.First_name, People.Last_name, Awards.[Award Name], 
    Recipients.Affiliation, Recipients.Recipient_Award_Comments, 
    Recipients.Recipient_Date, People.PersonID 
FROM
    People 
INNER JOIN 
    (Awards 
INNER JOIN 
    Recipients ON Awards.AwardID = Recipients.AwardID) ON People.PersonID = Recipients.PersonID;

查询2:

SELECT 
    Awards.[Award Name], People.First_name, People.Last_name, 
    Contenders.Contender_Date_Assigned, 
    Contenders.Award_Contender_Comments, People.PersonID
FROM 
    people, contenders, awards
WHERE 
    Awards.AwardID = Contenders.AwardID 
    AND People.PersonID = Contenders.PersonID;

我尝试在这些查询上使用左联接(在access上工作正常，但是在将数据迁移到SQL Server上)我收到此错误:

I tried using a left join on these queries ( which works fine on access , but on migrating the data to SQL Server) I am getting this error:

odbc调用失败[microsoft] [sql服务器本机客户端11.0] [sql服务器]不能绑定多部分标识符'Contenders.PersonID'，不能绑定'Contenders.AwardID'，并且不能'Awards.AwardID'约束.

odbc call failed [microsoft] [sql server native client 11.0] [sql server] the multipart identifier 'Contenders.PersonID' could not be bound, 'Contenders.AwardID' could not be bound and 'Awards.AwardID' could not be bound.

在进行内部联接时，效果很好，但这不是我想要的.

On doing an inner join it works fine but it isn't what I want.

查询r + c

SELECT 
    Query1.First_name, Query1.Last_name, Query1.[Award Name], 
    Query1.Affiliation, Query1.Recipient_Award_Comments, 
    Query1.Recipient_Date, Query2.First_name, Query2.Last_name, 
    Query2.[Award Name], Query2.Contender_Date_Assigned, 
    Query2.Award_Contender_Comments, Query1.PersonID
FROM
    Query1 
LEFT JOIN 
    Query2 ON Query1.PersonID = Query2.PersonID;

答

您可以:

with Query11 as
(
   <Query11 goes here>
), Query22 as
(
   <Query22 goes here>
)

select  q1.First_name, q1.Last_name, Query1.[Award Name], 
        q1.Affiliation, q1.Recipient_Award_Comments, 
        q1.Recipient_Date, q2.First_name, Query2.Last_name, 
        q2.[Award Name], q2.Contender_Date_Assigned, 
        q2.Award_Contender_Comments, q1.PersonID
from Query11 q1 left join
     Query22 q2
     on q2.PersonID = q1.PersonID;

我将Query11重写为:

SELECT p.First_name, p.Last_name, a.[Award Name], 
       r.Affiliation, r.Recipient_Award_Comments, 
       r.Recipient_Date, p.PersonID 
FROM People p INNER JOIN 
     Recipients r
     ON p.PersonID = r.PersonID INNER JOIN
     Awards a
     ON a.AwardID = r.AwardID;

odbc调用失败[microsoft] [sql server native client 11.0] [sql server]多部分标识符盘绕不绑定

相关推荐