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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With