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

Question

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;

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.