Screen Link:
https://app.dataquest.io/m/179/joining-data-in-sql/7/combining-joins-with-subqueries
My Code:
SELECT cities.name AS capital_city, facts.name AS country, cities.population
FROM facts
INNER JOIN (SELECT *
FROM cities
WHERE capital = 1
AND population > 10000000)
ON cities.facts_id = facts.id
ORDER BY 3 DESC
What I expected to happen:
I expected the code will output a table where the columns are: capital_city, Country, population (where population is greater than 10M)
What actually happened:
(sqlite3.OperationalError) no such column: cities.name
[SQL: SELECT cities.name AS capital_city, facts.name AS country, cities.population FROM facts INNER JOIN (SELECT * FROM cities WHERE capital = 1 AND population > 10000000) ON cities.facts_id = facts.id ORDER BY 3 DESC]
(Background on this error at: http://sqlalche.me/e/e3q8)
Since the codes are identical except for the naming of the columns (I used full name of the data table instead of alias, like I used cities.capital_city instead of c.capital_city), I wonder if it is a serious error? I looked my previous steps where I used the same notation for implementing the assignments and it did work ok, but not in this case. Have I missed anything important along the course?