Use variable in outer query in sub query

Question

Hi could you help me with my query I still get

The multi-part identifier "t.ID" could not be bound.

SELECT * FROM [Task].[Task_Entity] te
INNER JOIN [Task].[Task] t ON te.[TaskID] = t.[ID]
LEFT JOIN
(
SELECT * FROM [Task].[TaskInfo] derivedTable
WHERE derivedTable.[TaskID] = t.[ID]
) ti ON ti.[TaskID] = t.[ID]

Do you know any solution how to use t.[ID] in subquery?

Thank you for your replies.

Answer 1

The problem in the above T-SQL Statement is that even though we are using the Task table alias t in the subquery, we are not syntactically allowed to use it outside the scope of the subquery.

Try to replace

LEFT JOIN
(
SELECT * FROM [Task].[TaskInfo] derivedTable
WHERE derivedTable.[TaskID] = t.[ID]
)

by one of following INNER join

1. dont use derived table

INNER JOIN
 [Task].[TaskInfo] ti
ti.[TaskID] = t.[ID]

or

2. if it is necessary to have derived table, left your query for derived table without WHERE derivedTable.[TaskID] = t.[ID]

INNER JOIN
(
  SELECT * FROM [Task].[TaskInfo] derivedTable
)
 ti ON ti.[TaskID] = t.[ID]

This problem is solved in another thread too
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/3b2e0875-e98c-4931-bcb4-e9f449b637d7

Answer 2

If you would like English translation just let me know.

Jsme na českém fóru, proto si dovolím odpovědět česky.

Nahrazovat LEFT JOIN pomocí INNER JOIN dá zcela jistě odlišné výsledky, navrhuji ponechat LEFT JOIN, ale vyjmout zcela zbytečný subselect:

SELECT * FROM [Task].[Task_Entity] te
INNER JOIN [Task].[Task] t ON te.[TaskID] = t.[ID]
LEFT JOIN [Task].[TaskInfo] ti ON ti.[TaskID] = t.[ID]