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; 17 Answers
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.
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 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; 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.
0In 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.
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) 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
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. 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).
Instead you can try joining tables like,
select .... from dkcd right join a , b This should work
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; 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 ...
Did you forget to join some tables? If not then you probably need to use some aliases.
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.
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; 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 :-)
For me I was using wrong alias spellings , it worked after correct spelings
