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  lt  gt   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  lt  gt   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Instead you can try joining tables like   select          from     dkcd       right join                  a                     b   This should work

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt   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

User · Answer

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  lt  gt   99          AND LEFT phuongxa maxa  2    quanhuyen mahuyen ORDER BY maxa

User · Answer

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

[sql] The multi-part identifier could not be bound

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005