Table alias in nested query

[tom1234]

Hello

I am trying to write an SQL statement that uses two derived tables / subqueries in a following way:

SELECT * FROM tbl A
...
INNER JOIN
(SELECT * FROM tbl2
WHERE col =
(SELECT MIN(col2) FROM tbl2
WHERE col3 = A.acol ...))

The problem is that such a statement is syntactically incorrect: the A.acol column is not accessible and I am getting following error:

" The multi-part identifier "A.acol" could not be bound."

So the questions I am trying to answer are:
(a) is it possible to use a table alias in second level sql subquery
(b) if not, how to solve this problem simply?

Thank you very much!

Tom

[debasisdas]

Can you please post only your query part for my reference.

[tom1234]

Can you please post only your query part for my reference.

Sure. I can post directly the query causing the problem.

           SELECT TE.*,
            TE.HistoryId AS TreatmentEntryHistoryId,
            TE.HistoryValidToByUserId AS TreatmentEntryValidToByUserId,
            ET.*,
            [FoFormInstance].*
            FROM
            [History_PeTreatmentEntry] TE
 
            INNER JOIN
            (SELECT MAX(HistoryId) AS HistoryId FROM [History_PeTreatmentEntry] GROUP BY TreatmentEntryId) G
            ON TE.HistoryId = G.HistoryId
 
 
            INNER JOIN [History_PeTreatmentInstance]
            ON [History_PeTreatmentInstance].TreatmentInstanceId = TE.TreatmentInstanceId
 
 
            INNER JOIN
            (SELECT TEA.* FROM [History_PeTreatmentEntry] TEA
            WHERE HistoryId=
            (SELECT MIN(HistoryId)
            FROM [History_PeTreatmentEntry] Y
 
            -- Here is the problem! TE.TreatmentEntryId is syntactically incorrect
            WHERE Y.TreatmentEntryId = TE.TreatmentEntryId GROUP BY TreatmentEntryId)) FirstTE
 
            ON (TE.TreatmentEntryId = FirstTE.TreatmentEntryId)
            ....