Wow! That wasn't an easy problem, so congratulations! Now, there may be examples where the same table is used both in the main query and in the correlated subquery. Try to figure out what the following example returns:
FROM City AS MainCity
WHERE MainCity.Population > (
FROM City AS AverageCity
WHERE AverageCity.CountryID = MainCity.CountryID
In this example, for each country, we want to find cities with a population greater than the average population of all cities in that country. The problem is that we look for cities in the main clause and check the average population value for cities in the subquery. The same table appears twice—no good.
This is why we must use aliases for tables. Take a look: in the subquery, we put
... FROM City AS AverageCity ...
and in the main query, we put:
... FROM City AS MainCity ...
As you can see, we gave two different temporary names (aliases) to the
City table, one in the main query and one in the subquery. The alias comes after the table name, separated by a space. Remember, no commas here!