Why must the Select From is from Facts F

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

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”.

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

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

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

Ah, now i get it. Thanks @Sahil

1 Like

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

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