Skip to content
Advertisement

How to do query with multiple condition from 2 table?

I wanna pick cities from worldcity by these conditions:

  • the population of the city exceeds the population of the most populous city in the Filipina. (Meaning: for example city X is the city in the Filipina with the most population. We want to list the cities with a population greater than city X), AND
  • there is a city in the United States (uscity) with the same city name as the selected cities

worldcity.colomns = ['city', 'lat','lng','country','iso2','iso3','capital','population','id']

uscity.columns = ['city', 'state_id', 'state_name', 'county_fips', 'county_name',
   'lat', 'lng', 'population', 'density', 'source', 'military',
   'incorporated', 'timezone', 'ranking', 'zips', 'id']

query = """
SELECT DISTINCT
    city
FROM
    worldcity as w
inner join
      uscity as u
on
      w.city = u.city
WHERE w.population >= (MAX)population IN
    (SELECT
        population
    FROM worldcity
    WHERE
        country = 'Filipina';
    )
"""
sql_run(query)

OperationalError: near “population”: syntax error

Advertisement

Answer

Adjust the WHERE clause to place the MAX inside the subquery:

...
WHERE w.population > (
    SELECT MAX(population)
    FROM worldcity 
    WHERE country = 'Filipina'
)
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement