user2124871 user2124871 - 6 months ago 14
SQL Question

Subquery in Inner join - Multi-part identifer could not be bound

I have the following query and I am getting a multi-part identifier could not be bound exception. I realize why and my next thought was to use a CROSS APPLY, which didn't work. What I really need to do is join that subquery to make sure I get the most recent entry for a particular column.

Unfortunately, I've run out of ideas... How do I accomplish this query?

SELECT t1.*
FROM dbo.Table1 t1 (nolock)
INNER JOIN
(
SELECT TOP 1 t2_s.c1, t2_s.c2, t2_s.c4
FROM dbo.Table2 t2_s
WHERE t2_s.c1 = t1.c1
ORDER BY t2_s.dateStamped DESC
) t2
on t2.c1 = t1.c1
INNER JOIN dbo.Table3 t3
on t3.c1 = t2.c2 and t3.c2 = 1
WHERE t1.c2 = 'xxx'

Answer

Assuming you are using SQL Server, then CROSS APPLY should do what you want:

SELECT t1.*
FROM dbo.Table1 t1 (nolock) CROSS APPLY
     (SELECT TOP 1 t2_s.c1, t2_s.c2, t2_s.c4
      FROM dbo.Table2 t2_s
      WHERE t2_s.c1 = t1.c1
      ORDER BY t2_s.dateStamped DESC
     ) t2 INNER JOIN
     dbo.Table3 t3
     on t3.c1 = t2.c2 and t3.c2 = 1
WHERE t1.c2 = 'xxx';