179-7 Hows my code wrong

Q&A DQ Courses
#1

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

Your Code:

SELECT cities.name capital_city,
       facts.name country, cities.population 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: Get a table with capital city, country name, and population; capital cities with populations of over 10 million ordered from largest to smallest

What actually happened:

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

Other details: Hows my code wrong?
#2

Hey there!

It looks like what might be happening is that you’re using the alias cities to refer to your subquery, but you never assign that alias.

I would try adding AS cities after the closing parenthesis of your subquery, it should fix the problem you’re having.

You might ask, “Why can’t I refer to cities when I’ve used FROM cities inside my subquery?” The short answer is that a subquery is a self contained thing, so once the subquery is evaluated it no longer retains the information of what table(s) it came from, which is why you need to assign an alias here.

I hope this helps to fix your issue and explain what’s happening — if anything isn’t clear please let me know!

1 Like