How to do query with multiple condition from 2 table?

Question

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

select max(population) from (select population...)

Khanna111
– Khanna111

2022-02-25 23:27:25 +00:00
Commented Feb 25, 2022 at 23:27 — Khanna111
– Khanna111, Commented Feb 25, 2022 at 23:27

Parfait · Accepted Answer · 2022-02-26 01:46:15Z

0

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

...
WHERE w.population > (
    SELECT MAX(population)
    FROM worldcity 
    WHERE country = 'Filipina'
)

answered Feb 26, 2022 at 1:46

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Akbar Amelia Over a year ago

it's still error. PandaSQLException: (sqlite3.OperationalError) ambiguous column name: city

Parfait Over a year ago

That's a different error. In your SELECT clause qualify which table to use for city: w.city or u.city. Since you inner join both should be the same, so pick either.

Ichwan Maulana · Accepted Answer · 2022-05-31 14:03:50Z

0

I slightly changed the answer you wrote

Maybe this will help because I've tried it and it works

You can change ( > to >=) if you want to include the city with the highest population in the Philippines

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

answered May 31, 2022 at 14:03

Ichwan Maulana

11 bronze badge

Collectives™ on Stack Overflow

How to do query with multiple condition from 2 table?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related