469,909 Members | 1,805 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

Table alias in nested query

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
Jun 24 '07 #1
2 4976
debasisdas
8,127 Expert 4TB
Can you please post only your query part for my reference.
Jun 25 '07 #2
Can you please post only your query part for my reference.
Sure. I can post directly the query causing the problem.
Expand|Select|Wrap|Line Numbers
  1.            SELECT TE.*,
  2.             TE.HistoryId AS TreatmentEntryHistoryId,
  3.             TE.HistoryValidToByUserId AS TreatmentEntryValidToByUserId,
  4.             ET.*,
  5.             [FoFormInstance].*
  6.             FROM
  7.             [History_PeTreatmentEntry] TE
  8.  
  9.             INNER JOIN
  10.             (SELECT MAX(HistoryId) AS HistoryId FROM [History_PeTreatmentEntry] GROUP BY TreatmentEntryId) G
  11.             ON TE.HistoryId = G.HistoryId
  12.  
  13.  
  14.             INNER JOIN [History_PeTreatmentInstance]
  15.             ON [History_PeTreatmentInstance].TreatmentInstanceId = TE.TreatmentInstanceId
  16.  
  17.  
  18.             INNER JOIN
  19.             (SELECT TEA.* FROM [History_PeTreatmentEntry] TEA
  20.             WHERE HistoryId=
  21.             (SELECT MIN(HistoryId)
  22.             FROM [History_PeTreatmentEntry] Y
  23.  
  24.             -- Here is the problem! TE.TreatmentEntryId is syntactically incorrect
  25.             WHERE Y.TreatmentEntryId = TE.TreatmentEntryId GROUP BY TreatmentEntryId)) FirstTE
  26.  
  27.             ON (TE.TreatmentEntryId = FirstTE.TreatmentEntryId)
  28.             ....
  29.  
Jun 25 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Mahesh Hardikar | last post: by
5 posts views Thread by Arvin Portlock | last post: by
3 posts views Thread by Rodríguez Rodríguez, Pere | last post: by
3 posts views Thread by Frank Swarbrick | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.