Skip to content
Fix Code Error

The multi-part identifier could not be bound

March 13, 2021 by Code Error
Posted By: Anonymous

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;

Solution

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.

Answered By: Anonymous

Related Articles

  • SQL query return data from multiple tables
  • SQL JOIN and different types of JOINs
  • Can't find why this datetime test fails, in F#
  • COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?
  • SQL find sum of entries by date including previous date
  • Gradle error: Execution failed for task…
  • org.gradle.api.tasks.TaskExecutionException:…
  • SQL Server: Query fast, but slow from procedure
  • How do I include certain conditions in SQL Count
  • Converting between datetime, Timestamp and datetime64
  • Pandas Merging 101
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • exec failed because the name not a valid identifier?
  • Sorting a 2D string array in c
  • How to find column names for all tables in all…
  • Average values between two dates by group
  • Jetpack Compose and Hilt Conflict
  • Execution failed for task…
  • mongodb group values by multiple fields
  • Using DISTINCT along with GROUP BY in SQL Server
  • Database development mistakes made by application developers
  • How to convert image into byte array and byte array…
  • INNER JOIN vs LEFT JOIN performance in SQL Server
  • laravel vuejs/axios put request Formdata is empty
  • Left Outer Join using + sign in Oracle 11g
  • Rewrite left outer join involving multiple tables…
  • LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  • type object 'datetime.datetime' has no attribute 'datetime'
  • Select bottom n records and concatenate in same row
  • Change multiple SVG paths in CSS on hover or with javaScript
  • get UTC timestamp in python with datetime
  • LINQ with groupby and count
  • Generate SQL Create Scripts for existing tables with Query
  • .Net Core 3.1 Entity Framework Slow Query Problem
  • Using DateTime in a SqlParameter for Stored…
  • error LNK2005: ✘✘✘ already defined in…
  • What is the difference between "INNER JOIN" and…
  • Filter by field on relationship in SQLAlchemy
  • Difference between System.DateTime.Now and…
  • Select distinct values in each 150 columns of a table
  • When I'm testing a web app by JUnit and Mockito I…
  • Conditional WHERE clauses with TVP parameter
  • Does the join order matter in SQL?
  • Is there a way to join two queries in SQL each with…
  • Using IS NULL or IS NOT NULL on join conditions -…
  • PostgreSQL: No function matches the given name and…
  • "import datetime" v.s. "from datetime import datetime"
  • SQLGrammarException:error executing work ORA-01722:…
  • Angular: Can't find Promise, Map, Set and Iterator
  • SQL select only rows with max value on a column
  • SQL left join vs multiple tables on FROM line?
  • Union of multiple Database queries with same parameters
  • How to make an unaware datetime timezone aware in python
  • Replace Default Null Values Returned From Left Outer Join
  • How to convert number to words in java
  • How to truncate milliseconds off of a .NET DateTime
  • python 3.2 UnicodeEncodeError: 'charmap' codec can't…
  • Spark EMR job jackson error -…
  • How do I obtain a Query Execution Plan in SQL Server?
  • LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
  • Sort table rows In Bootstrap
  • Is this request generated by EF Core buggy or is it my code?
  • How to return rows from left table not found in right table?
  • C++ OpenGL stb_image.h errors
  • How to select rows based on two columns creating an…
  • LINQ left join generates wrong SQL query
  • Should MySQL have its timezone set to UTC?
  • How do I count unique visitors to my site?
  • Avoid multiple copy of data when composing objects…
  • Using Auto Layout in UITableView for dynamic cell…
  • How to pull out an element to the right in…
  • Addition of two queries - SQL/HANA
  • How to use 2 columns as "key" to get MAX value of…
  • Best way to select random rows PostgreSQL
  • GROUP BY + CASE statement
  • display other inputs based on the value of another…
  • How can a LEFT OUTER JOIN return more records than…
  • How can I SELECT rows with MAX(Column value),…
  • SQL Server query to find all permissions/access for…
  • How can I fix MySQL error #1064?
  • What is the scope of variables in JavaScript?
  • VueJS components ref is undefined at all stages
  • Comparing two dataframes with some entries missing
  • Convert a python UTC datetime to a local datetime…
  • Bootstrap Card - change width
  • What is the standard way to add N seconds to…
  • SQL Query - SUM(CASE WHEN x THEN 1 ELSE 0) for…
  • PHP parse/syntax errors; and how to solve them
  • MySQL CONCAT returns NULL if any field contain NULL
  • Keeping timezone when saving datetime in dataframe as csv
  • Flex: REJECT rejects one character at a time?
  • SQL Server query - Selecting COUNT(*) with DISTINCT
  • Fix top buttons on scroll of list below
  • LINQ orderby on date field in descending order
  • Creating a typescript definition file for a vue…
  • How to query for Xml values and attributes from…
  • Form field border-radius is not working only on the…
  • What is a 'multi-part identifier' and why can't it be bound?
  • How to do a nested COALESCE subquery in a join for 3…
  • Combine two queries to count distinct strings with…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

SQL query return data from multiple tables

Next Post:

Is there any way to kill a Thread?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error