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;

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.