C.names vs cities.name is there a difference?

Q&A DQ Courses
,
#1

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?

#2

Hello @ntokoldoshev.

The problem is that you forgot to assign the name cities to the query you wrote inside the INNER JOIN. Do it like this:

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) AS cities
                ON cities.facts_id = facts.id
    ORDER BY 3 DESC

Note that I added AS cities.

You can use the name you want, but you need to inform the database that you are doing so.

Hope this is helpful.

1 Like
#3

It would be nice of you if you could leave the topic so other people running into the same problem could search and find this answer. This is a legit question about a common problem.

#4

Sure. You are right!

1 Like