Why can't I use a table alias in a subquery?

Question

I have a MS Access database. When I try to run this query:

SELECT *
FROM 
(
  SELECT * 
  FROM tblClients AS a1 
  LEFT JOIN 
  (
    SELECT TOP 1 lVersion, lRequestID AS lVerReqId, lClientId    
    FROM tblClientsVersionsHistory 
    WHERE ID = (
      SELECT MAX(ID) 
      FROM tblClientsVersionsHistory 
      WHERE lClientId = a1.Id
    )
  ) AS a2
  ON a1.Id = a2.lClientId
)

It suggest me to enter a1.Id, but I want to use it from (SELECT * FROM tblClients AS a1).

Answer 1

The a1 alias belongs to a table to which the a2 derived table is being joined, and you are trying to reference the alias from inside the derived table. You cannot do that in SQL. This is not just a limitation of MS Access.

If it was a correlated subquery and the alias in question was assigned at any of the outer levels, such a reference would be valid, but a derived table is not allowed to reference aliases of any of the tables it is being joined to. You may reference them in the derived table's ON joining predicate. In this case, a1.Id is a valid reference in ON a1.Id = a2.lClientId, and it would be equally valid if it was used in a correlated subquery in the ON clause, for instance:

ON EXISTS
  (
    SELECT
      ... something something ...
      ... a1.Id ...
      ...
  )

That would work. But such references cannot be resolved inside the derived table itself. As a result, you cannot reference a1 inside a2.