CODEWITHSUNDEEP
sqlsql-serversql-server-2005
PhamMinh
PhamMinh

Reputation: 2665

Multiple joins giving "The multi-part identifier could not be bound"

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?

If I divide the query into 2 individual queries, it runs 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;

Upvotes: 257

Views: 1239446

Answers (18)

user1007074
user1007074

Reputation: 2586

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

Upvotes: -2

Honza P.
Honza P.

Reputation: 1242

For me the issue was that I was calling a DB function without empty brackets select [apo].[GenerateNationalIdFrance] instead of select [apo].[GenerateNationalIdFrance]().

Upvotes: -1

John Lord
John Lord

Reputation: 2185

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.

Upvotes: 0

Alexander Zaldostanov
Alexander Zaldostanov

Reputation: 3011

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;

Upvotes: 23

Andrew
Andrew

Reputation: 20081

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;

Upvotes: 1

SauerTrout
SauerTrout

Reputation: 499

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)

Upvotes: 5

Tore Aurstad
Tore Aurstad

Reputation: 3816

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.

Upvotes: 1

Hashim Akhtar
Hashim Akhtar

Reputation: 881

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.

Upvotes: 6

xbmono
xbmono

Reputation: 2316

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

Upvotes: 2

ttt
ttt

Reputation: 6809

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

Upvotes: 0

Zolfaghari
Zolfaghari

Reputation: 1323

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

Upvotes: 1

Pavel M.
Pavel M.

Reputation: 572

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.

Upvotes: 15

SVaidya
SVaidya

Reputation: 321

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;

Upvotes: 0

Suman Kumar
Suman Kumar

Reputation: 21

Instead you can try joining tables like,

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

This should work

Upvotes: 1

CPHPython
CPHPython

Reputation: 13719

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

Upvotes: 1

Bogartz
Bogartz

Reputation: 11

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.

Upvotes: 1

Bob
Bob

Reputation: 23000

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

Upvotes: 51

Andriy M
Andriy M

Reputation: 77667

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.

Upvotes: 287

Related Questions