Act fast, special offers end soon! Up to $294 is savings when you get Premium today.
Get offer codes

Why can't i refer to alias for the Subquery

Screen Link: https://app.dataquest.io/m/179/joining-data-in-sql/7/combining-joins-with-subqueries

Your Code:

SELECT c.name capital_city, f.name country, c.population 
FROM facts f
INNER JOIN (
            SELECT * FROM cities 
            WHERE c.capital = 1 
            AND f.population > 10000000
            ) c ON c.facts_id = f.id
ORDER BY 3 DESC

What I expected to happen: Using a join and a subquery, write a query that returns capital cities with populations of over 10 million ordered from largest to smallest. Include the following columns:

  • capital_city - the name of the city.
  • country - the name of the country the city is the capital of.
  • population - the population of the city.

What actually happened:

(sqlite3.OperationalError) no such column: c.capital
[SQL: SELECT c.name capital_city, f.name country, c.population  FROM facts f INNER JOIN (             SELECT * FROM cities              WHERE c.capital = 1              AND f.population > 10000000             ) c ON c.facts_id = f.id ORDER BY 3 DESC]
(Background on this error at: http://sqlalche.me/e/e3q8)

Other details: i used the alias for the inner join subquery. Is it because the subquery comes first that’s why the alias didn’t work?

Exactly! Subqueries run first, the creation of the alias for the subquery happens “outside” of the subquery. Similarly, the subquery won’t know what f is.

Great thank you so much Bruno. helps to understand why it wasn’t working :grinning: