The multi-part identifier could not be bound

问题:

I've seen similar errors on SO, but I don't find a solution for my problem. I have a SQL query like:

SELECT DISTINCT
        a.maxa ,
        b.mahuyen ,
        a.tenxa ,
        b.tenhuyen ,
        ISNULL(dkcd.tong, 0) AS tongdkcd
FROM    phuongxa a ,
        quanhuyen b
        LEFT OUTER JOIN ( SELECT    maxa ,
                                    COUNT(*) AS tong
                          FROM      khaosat
                          WHERE     CONVERT(DATETIME, ngaylap, 103) BETWEEN 'Sep 1 2011'
                                                              AND
                                                              'Sep 5 2011'
                          GROUP BY  maxa
                        ) AS dkcd ON dkcd.maxa = a.maxa
WHERE   a.maxa <> '99'
        AND LEFT(a.maxa, 2) = b.mahuyen
ORDER BY maxa;

When I execute this query, the error result is: The multi-part identifier "a.maxa" could not be bound. Why?
P/s: if i divide the query into 2 individual query, it run ok.

SELECT DISTINCT
        a.maxa ,
        b.mahuyen ,
        a.tenxa ,
        b.tenhuyen
FROM    phuongxa a ,
        quanhuyen b
WHERE   a.maxa <> '99'
        AND LEFT(a.maxa, 2) = b.mahuyen
ORDER BY maxa;

and

SELECT  maxa ,
        COUNT(*) AS tong
FROM    khaosat
WHERE   CONVERT(DATETIME, ngaylap, 103) BETWEEN 'Sep 1 2011'
                                        AND     'Sep 5 2011'
GROUP BY maxa;

回答1:

You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly.

The thing is, explicit joins (the ones that are implemented using the JOIN keyword) take precedence over implicit ones (the 'comma' joins, where the join condition is specified in the WHERE clause).

Here's an outline of your query:

SELECT
  …
FROM a, b LEFT JOIN dkcd ON …
WHERE …

You are probably expecting it to behave like this:

SELECT
  …
FROM (a, b) LEFT JOIN dkcd ON …
WHERE …

that is, the combination of tables a and b is joined with the table dkcd. In fact, what's happening is

SELECT
  …
FROM a, (b LEFT JOIN dkcd ON …)
WHERE …

that is, as you may already have understood, dkcd is joined specifically against b and only b, then the result of the join is combined with a and filtered further with the WHERE clause. In this case, any reference to a in the ON clause is invalid, a is unknown at that point. That is why you are getting the error message.

If I were you, I would probably try to rewrite this query, and one possible solution might be:

SELECT DISTINCT
  a.maxa,
  b.mahuyen,
  a.tenxa,
  b.tenhuyen,
  ISNULL(dkcd.tong, 0) AS tongdkcd
FROM phuongxa a
  INNER JOIN quanhuyen b ON LEFT(a.maxa, 2) = b.mahuyen
  LEFT OUTER JOIN (
    SELECT
      maxa,
      COUNT(*) AS tong
    FROM khaosat
    WHERE CONVERT(datetime, ngaylap, 103) BETWEEN 'Sep 1 2011' AND 'Sep 5 2011'
    GROUP BY maxa
  ) AS dkcd ON dkcd.maxa = a.maxa
WHERE a.maxa <> '99'
ORDER BY a.maxa

Here the tables a and b are joined first, then the result is joined to dkcd. Basically, this is the same query as yours, only using a different syntax for one of the joins, which makes a great difference: the reference a.maxa in the dkcd's join condition is now absolutely valid.

As @Aaron Bertrand has correctly noted, you should probably qualify maxa with a specific alias, probably a, in the ORDER BY clause.

回答2:

Sometimes this error occurs when you use your schema (dbo) in your query in a wrong way.

for example if you write:

select dbo.prd.name
from dbo.product prd

you will get the error.

In this situations change it to:

select prd.name
from dbo.product prd

回答3:

if you have given alies name change that to actual name

for example

SELECT  
    A.name,A.date
  FROM [LoginInfo].[dbo].[TableA] as A
   join 
  [LoginInfo].[dbo].[TableA] as B 
  on  [LoginInfo].[dbo].[TableA].name=[LoginInfo].[dbo].[TableB].name;

change that to

SELECT  
    A.name,A.date
  FROM [LoginInfo].[dbo].[TableA] as A
   join 
  [LoginInfo].[dbo].[TableA] as B 
  on  A.name=B.name;

回答4:

I was struggling with the same error message in SQL SERVER, since I had multiple joins, changing the order of the joins solved it for me.

回答5:

In my case the issue turned out to be the alias name I had given to the table. "oa" seems to be not acceptable for SQL Server.

回答6:

I was having the same error from JDBC. Checked everything and my query was fine. Turned out, in where clause I have an argument:

where s.some_column = ?

And the value of the argument I was passing in was null. This also gives the same error which is misleading because when you search the internet you end up that something is wrong with the query structure but it's not in my case. Just thought someone may face the same issue

回答7:

What worked for me was to change my WHERE clause into a SELECT subquery

FROM:

    DELETE FROM CommentTag WHERE [dbo].CommentTag.NoteId = [dbo].FetchedTagTransferData.IssueId

TO:

    DELETE FROM CommentTag WHERE [dbo].CommentTag.NoteId = (SELECT NoteId FROM FetchedTagTransferData)

回答8:

I'm new to SQL, but came across this issue in a course I was taking and found that assigning the query to the project specifically helped to eliminate the multi-part error. For example the project I created was CTU SQL Project so I made sure I started my script with USE [CTU SQL Project] as my first line like below.

USE [CTU SQL Project]
SELECT Advisors.First_Name, Advisors.Last_Name...and so on.

回答9:

If this error happens in an UPDATE, double-check the JOIN on the table with the column/field that is causing the error.

In my case this was due to the lack of the JOIN itself, which generated the same error due to an unknown field (as Andriy pointed out).

回答10:

Instead you can try joining tables like,

select 
  .... 
from 
   dkcd 
     right join 
                a
                  , b

This should work

回答11:

SELECT DISTINCT
        phuongxa.maxa ,
        quanhuyen.mahuyen ,
        phuongxa.tenxa ,
        quanhuyen.tenhuyen ,
        ISNULL(dkcd.tong, 0) AS tongdkcd
FROM    phuongxa ,
        quanhuyen
        LEFT OUTER JOIN ( SELECT    khaosat.maxa ,
                                    COUNT(*) AS tong
                          FROM      khaosat
                          WHERE     CONVERT(DATETIME, ngaylap, 103) BETWEEN 'Sep 1 2011'
                                                              AND
                                                              'Sep 5 2011'
                          GROUP BY  khaosat.maxa
                        ) AS dkcd ON dkcd.maxa = maxa
WHERE   phuongxa.maxa <> '99'
        AND LEFT(phuongxa.maxa, 2) = quanhuyen.mahuyen
ORDER BY maxa;

回答12:

My error was to use a field that did not exist in table.

table1.field1 => is not exist

table2.field1 => is correct

Correct your Table Name.

my error occurred because of using WITH

WITH RCTE AS (
   SELECT...
)
SELECT RCTE.Name, ...
FROM 
  RCTE INNER JOIN Customer
  ON RCTE.CustomerID = Customer.ID 

when used in join with other tables ...

回答13:

Did you forget to join some tables? If not then you probably need to use some aliases.

回答14:

I was also struggling with this error and ended up with the same strategy as the answer. I am including my answer just to confirm that this is a strategy that should work.

Here is an example where I do first one inner join between two tables I know got data and then two left outer joins on tables that might have corresponding rows that can be empty. You mix inner joins and outer joins to get results with data accross tables instead of doing the default comma separated syntax between tables and miss out rows in your desired join.

use somedatabase
go 

select o.operationid, o.operatingdate, p.pasid, p.name as patientname, o.operationalunitid, f.name as operasjonsprogram,  o.theaterid as stueid, t.name as stuenavn, o.status as operasjonsstatus from operation o 
inner join patient p on o.operationid = p.operationid 
left outer join freshorganizationalunit f on f.freshorganizationalunitid = o.operationalunitid
left outer join theater t on t.theaterid = o.theaterid
where (p.Name like '%Male[0-9]%' or p.Name like '%KFemale [0-9]%')

First: Do the inner joins between tables you expect to have data matching. Second part: Continue with outer joins to try to retrieve data in other tables, but this will not filter out your result set if table outer joining to has not got corresponding data or match on the condition you set up in the on predicate / condition.