The multi-part identifier could not be bound

Question

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;

Disembogue

Does the phuongxa table include a column maxa? – Sincerity

What happens if you add group by maxa, tong -- right after Sep 5 2011. – Disadvantaged

Yes,it have. If I devide the query to 2 subquery,it run ok – Disembogue

Sounds like you are executing on the wrong database. Add a "USE [database name]" statement to the beginning of the query and see if you still get the error. – Episcopalian

No,I had said above, if i divide the query into 2 individual query,it run okey. – Disembogue

Post the two queries that DO work, or your schema. Your order by clause is wrong (maxa is ambiguous, since more than one table has that column) but that would not result in the error you posted. – Schriever

You can also get this error message if you use CTE's and are processing recursive self join queries via union all on a parent id and forget to apply a cast. – Mawson

Answer 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.

Hum

ORDER BY maxa is still ambiguous, no? Also I'd be careful with 'Sep 1 2011' as a date, won't work with different language/regional settings. – Justness

@Aaron: Agree about ORDER BY maxa, thanks. As for the dates, I believe that's how the OP has chosen to specify them in their environment. – Hum

"explicit joins... take precedence over implicit ones" -- can you provide a citation for this, please? e.g. is that defined in SQL Standards or is it a product feature? Thanks. – Cholula

@onedaywhen: I'm afraid this is no more than an observation on my side so far. I'm somewhat relieved by the fact that I am not first to talk about precedence of joins here, but other than that, I would be glad to find any kind of official confirmation myself. – Hum

Oh great: I get to pour over the SQL-92 spec :) Thanks for the hint, though. – Cholula

In my case, I was forgetting to put spaces when I concatenated strings to build the sql, so 'FROM dbo.table_a a' + 'INNER JOIN dbo.table_b b' became 'FROM dbo.table_a aINNER JOIN dbo.table_b b', and it got confused and gave me this error message. Details, details, details. – Flick

@GuySchalnat: Interesting! That's perhaps one of the very rare cases where omitting INNER might be viewed as justified by those who are very much into conciseness. I mean, if you'd used just JOIN, you would've ended up with FROM dbo.table_a aJOIN dbo.table_b b, which would be a syntax error. A "Syntax error" message would seem more helpful in spotting the issue in such cases. But IMHO that would be a weak argument (for conciseness, I mean), and I personally would've still preferred being explicit with my joins. Thanks for sharing! – Hum

@AndriyM Good point, but I still prefer INNER JOIN anyway. I'm actually writing in C# (I SQLized it for the comment), and I should have known better when building strings to not use spaces. I just commented in case someone else arrived here with the same problem (I did it again and didn't remember my mistake last time). – Flick

If anyone else comes here looking how to do it without conditions for table A and B, it is called CROSS JOIN: SELECT * FROM a CROSS JOIN b [other joining] – Chaste

Answer 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

Tender

This is quite annoying one and took me way too long to figure it out. Thanx. Most annoying part is sometimes it naggs about this but other times it passes normaly – Zoo

Answer 3

if you have given alias 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;

Smelser

Also if you're building up the sql string, watch out of lack of trailing spaces at the end of line. It converted my alias M into MINNER when it joined the next INNER JOIN line below. SQL profiler showing the string executed helped solve my issue. (Commented here as it is related to the alias v actual name issue) – Biannual

wow @Biannual thank you I didnt even think about this and was banging my head against the wall trying to figure out why my query wasnt working, i was missing a space at the end of one of the string carriage returns! – Paronym

Answer 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.

Ceratodus

Answer 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.

Debutante

Answer 6

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)

Punctilious

Answer 7

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

Robrobaina

Answer 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.

Melitamelitopol

When you say "project" I assume you mean database and not projection. The use statement simply changes which database you are scoping the query to – Rea

Yes, Charleh project as in the database I was working. I wasn't sure what I was doing wrong with my database, but stating "use" and the specific database to scope eliminated my error. – Melitamelitopol

Answer 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).

Gibbie

Answer 10

Instead you can try joining tables like,

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

This should work

Golightly

Answer 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;

Banshee

Use table names itself instead of using alias if multi-part bound issue comes up. – Banshee

Answer 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 ...

Expertise

Answer 13

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

Cloudscape

Answer 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.

Staghound

Answer 15

This error can also be caused by simply missing a comma , between the column names in the SELECT statement.

eg:

SELECT MyCol1, MyCol2 MyCol3 FROM SomeTable;

Unlawful

If SELECT MyCol1, MyCol2, MyCol3 FROM SomeTable; works, then I don't see how SELECT MyCol1, MyCol2 MyCol3 FROM SomeTable; can give you any error, never mind the one in the title of this question. Unless there's more to your example that meets the eye, in which case I'm guessing it wouldn't be simply missing a comma. Do you think you could come up with a simple scenario reproducing the issue you are talking about? Here's where I tried to simply omit a comma, and it still worked (= didn't error out): dbfiddle.uk/… – Hum

@AndriyM The only thing I can think of is that my query probably was more complex (maybe with joins, sorry a long time ago.) But I am sure the fix for the error message I received (which may or may not have been exactly as is in the OP's title) was to just put in the missing comma! – Unlawful

I forgot the comma between the table names in the FROM clause on a very simple query and got the error. – Tin

Answer 16

For me the issue was that I was stupidly calling a DB function without empty brackets select [apo].[GenerateNationalIdFrance] instead of select [apo].[GenerateNationalIdFrance]() ... took me few minutes to realize that but worth mentioning for juniors out there :-)

Anthropometry

Answer 17

We received this error when we referenced a table column through a table alias but forgot to actually alias the table. "Select * from tblOrder where o.id > 1000" failed for obvious reasons after you look at it.

Sebrinasebum

Answer 18

Looking at the variety of different answers to this question confirms the vague nature of the error message. It gives a clue as to the root cause of the problem but not a definite answer.

I am fortunate to have a model of my schema in a .Net Sql Project. This can be generated relatively easily from an existing database schema if you don't have one. I put the freestyle query that was giving me this error message into a new file within the project. (e.g. Create View [dbo].[Test] as Select...) Visual Studio kindly highlighted the error for me with a SQLxxxxx error number and description which helped me resolve the issue.

Potentially other IDEs might offer similar functionality

Distrust

Well, this is "not a definite answer" either. Not an answer anyway. It only describes behavior of some IDE. – Casaubon

Not all problems have definite solutions. I have described a procedural step that can be tried by other people who encounter the error message in the OP's question. In the absence of any other input, it could help them get to the bottom of it. If they've read all the other answers, it might guide them towards which possible solution to try first. I've managed to solve issues myself with SO answers that you would have called "not an answer" plenty of times. If you feel strongly about it, you should downvote my answer – Distrust

Answer 19

For me I was using wrong alias spellings , it worked after correct spelings

Chafin