MySQL Alias Question

alias, mysql, mysql-error-1054, sql

Solution

In order to execute a correlated subquery, you need an alias for the outer table. You created an alias for the outer table's field. Take a look at the corrected code below, that has an alias for the table (Cou) that is referenced in the subquery (note that the field alias is not required so I removed it. There's no harm in adding it back if you wish):

SELECT Continent, Name, SurfaceArea
FROM Country Cou
WHERE SurfaceArea = 
(
    SELECT MAX(SurfaceArea)
    FROM Country
    WHERE Continent = Cou.Continent
);

Regarding the usage of AS, it's optional. For example, in the query above you could write `Country AS Cou`, it would be the same.

Problem

i am wondering why this fails ``` mysql> SELECT Continent C, Name, SurfaceArea -> FROM Country -> WHERE SurfaceArea = ( -> SELECT MAX(SurfaceArea) -> FROM Country -> WHERE Continent = C); ERROR 1054 (42S22): Unknown column 'C' in 'where clause' ``` its a answer provided by the certification guide for some sample exercises. btw, for alias when do i have to use AS? isit optional?

Original source