problem with alias in subquery

[attaboy]

This doesn't work on my system

mysql 5.5.16

ERROR 1054(42s22): unknown column 'c' in 'where clause'

 

SELECT Code c, Name
FROM Country
WHERE Continent = 'Europe'
AND EXISTS (SELECT *
      FROM CountryLanguage
      WHERE CountryCode = c AND Language = 'Spanish');

 

The error message states there's an unknown column 'c' in the where clause.

can't subqueries handle aliases?

[gizmola]

Yes they can.  You have a syntax issue.  As best as I can tell, all you really want in the subquery is:

 

SELECT *
      FROM CountryLanguage
      WHERE Language = 'Spanish'

[attaboy]

thanks for answering but that only gives me all the countries in Europe.

[gizmola]

That is because your WHERE clause in the outer query is restricing them:

 

WHERE Continent = 'Europe'

[attaboy]

But the whole idea is to find only countries in Europe where Spanish is spoken.

The original code I posted here is copied from the sample code of my textbook which is MySQL 5.0 Study Guide from MySQL Press

I'm trying to determine if the book is wrong and you can't  access an alias defined in an outer query from an inner query or if just I can't do that.  Except the alias issue the query works fine. If in the inner query I replace the 'c' with 'country.Code' it works.

[attaboy]

I talked with my instructor and apparently the book is in error and it's a scope issue.