The multi-part identifier could not be bound - SubQuery

Question

Schema:

create table TableA (A1 int)
create table TableB (B1 int, B2 int)
create table TableC (C1 int)

Problematic query:

SELECT * 
FROM TableA a
INNER JOIN TableB b ON b.B1=a.A1
INNER JOIN (SELECT TOP 1 * 
            FROM TableC c
            WHERE c.C1=b.B1 ORDER BY c.C1) d ON d.C2=b.B2
INNER JOIN OtherTable ON OtherTable.Foo=d.C1

Building this schema and running the query in SQLFiddle under SQL Server 2008 results in:

The multi-part identifier "b.B1" could not be bound.: SELECT * FROM TableA a INNER JOIN TableB b ON b.B1=a.A1 INNER JOIN (SELECT TOP 1 * FROM TableC c WHERE c.C1=b.B1 ORDER BY c.C1) d ON d.C2=b.B2

Using CROSS APPLY instead of INNER JOIN for the subquery fixes the issue

What's the problem?

Edit: I added "TOP 1" that was part of the real query and it's a relevant part of the problem.

Edit2: Further information about the problem.

Solution 1

you can't reference from JOIN clause to another part of JOIN.

Use this instead.

SELECT * 
FROM TableA a
INNER JOIN TableB b
    ON b.B1=a.A1
INNER JOIN TableC c
    ON d.C2=b.B2
      AND c.C1=b.B1

EDITED

SELECT * 
FROM TableA a
INNER JOIN TableB b ON b.B1=a.A1
WHERE b.B2 = (SELECT TOP 1 c.C2 
               FROM TableC c
               WHERE c.C1=b.B1 ORDER BY c.C1)

For further use of TableC in JOIN-s you can use this.

SELECT * 
FROM TableA a
    INNER JOIN TableB b
        ON b.B1=a.A1
    INNER JOIN
       (
           SELECT
               ROW_NUMBER() OVER (PARTITION BY C1 ORDER BY C2) RN,
               C2
               --, other columns fromTableC if needed
           FROM TableC
       ) CC
     ON b.B2 = CC.C2
       AND CC.RN = 1

Solution 2

You cannot access an alias from a join inside of another joined subquery. You will need to use the following which joins the subquery on two columns/tables:

SELECT * 
FROM TableA a
INNER JOIN TableB b 
  ON b.B1=a.A1
INNER JOIN 
(
  SELECT * 
  FROM TableC c
) d 
  ON d.C2=b.B2
  AND d.C1 = b.B1

Or this can be written as:

SELECT * 
FROM TableA a
INNER JOIN TableB b 
  ON b.B1=a.A1
INNER JOIN TableC c
  ON c.C2=b.B2
  AND c.C1 = b.B1

Comments

[ClearCloud8]

Using CROSS APPLY (or OUTER APPLY in my case instead of a LEFT JOIN)... resolved this issue for me!

[Mmm]

CROSS APPLY was new to me, but solved my challenge! Thanks for posting this.

[Hamlet Hakobyan]

You cannot access an alias inside of a subquery. I don't think so. SELECT * FROM Table1 T1 WHERE someColumn IN (SELECT someColumn FROM Table2 WHERE T1.Id = Table1_Id )

[Matthew Azkimov]

What if the "SELECT TOP 1" is needed in further inner joins?

[Matthew Azkimov]

Using "ROW_NUMBER()" is criminal.

[Hamlet Hakobyan]

Using "ROW_NUMBER()" is criminal. Why?

[Yehuda Makarov]

From a join inside of another joined sub query - was their point I believe