MySQL: need assistance with MySQL logic

Question

I am trying to work through a MySQL problem and I almost have it... The Question is...

What query would you run to get all the cities in Mexico with a population of greater than 500,000? Arrange the result by population in descending order.

Here is the table I am working off of...

My current code...

SELECT cities.name, cities.population
FROM countries
LEFT JOIN cities
ON countries.id = cities.country_id
WHERE cities.population < 500000

I feel like I should be super close. Any pointers?

Answer 1

Figured it out. All I needed was a little more detail!

Old code...

SELECT cities.name, cities.population
FROM countries
LEFT JOIN cities
ON countries.id = cities.country_id
WHERE cities.population < 500000

New code...

SELECT cities.name, cities.population
FROM countries 
LEFT JOIN cities
ON countries.id = cities.country_id
WHERE countries.name = 'Mexico' AND cities.population < 500000
GROUP BY cities.name
ORDER BY cities.name DESC

Answer 2

And where is the problem? Looks simple ...without testing ;)

select t1.name, t1.population from countries t0 join cities t1 on t0.id = t1.country_id and t1.population > 500000 order by t1.population desc