Why must the Select From is from Facts F

Q&A DQ Courses
, ,
#1

Screen Link: https://app.dataquest.io/m/179/joining-data-in-sql/6/finding-the-most-populous-capital-cities
Your Code:

SELECT c.name as capital_city, f.name as country, c.population as population FROM cities as c
INNER JOIN cities as c ON f.id = c.facts_id
WHERE c.capital == 1
ORDER BY 3 DESC
LIMIT 10

What I expected to happen: Able to return a query that returns the 10 capital cities with the highest population ranked from biggest to smallest population.

What actually happened:

[SQL: SELECT c.name as capital_city, f.name as country, c.population as population FROM cities as c INNER JOIN cities as c ON f.id = c.facts_id WHERE c.capital == 1 ORDER BY 3 DESC LIMIT 10]
(Background on this error at: http://sqlalche.me/e/e3q8)

Other details: From the answer, the error is

SELECT c.name capital_city, f.name country, c.population
FROM facts f
INNER JOIN cities c ON c.facts_id = f.id
WHERE c.capital = 1
ORDER BY 3 DESC
LIMIT 10

since i am taking columns from both facts and cities, why can’t i select the FROM cities c

#2

Hi,

You have a typo in your join since facts is not being queried at all. I believe you meant to do “inner join facts as f”.

#3

you mean this liner? sorry as i can’t see the problem here

INNER JOIN cities as c ON f.id = c.facts_id
#4

Hi @willx,

SELECT c.name as capital_city, f.name as country, c.population as population FROM cities as c
INNER JOIN cities as c ON f.id = c.facts_id

It should be:

SELECT c.name as capital_city, f.name as country, c.population as population FROM facts as f
INNER JOIN cities as c ON f.id = c.facts_id

or:

SELECT c.name as capital_city, f.name as country, c.population as population FROM cities as c
INNER JOIN facts as f ON f.id = c.facts_id

Best,
Sahil

#5

Ah, now i get it. Thanks @Sahil

1 Like
#6

I’m using this as my query but seem to be getting an error. “The value for result doesn’t look right” Can you help me with this?

SELECT c.name AS capital_city,
f.name AS country,
f.population AS population
FROM facts AS f
INNER JOIN cities AS c ON f.id = c.facts_id
WHERE c.capital = 1
ORDER BY 3 DESC
LIMIT 10

#7

Hi @thomas.palesky,

As per instruction:
population , the population of the city.

You should display city population instead of country population.

Best,
Sahil

1 Like