CODEWITHSUNDEEP
mysqlwhere-clause
felipe343
felipe343

Reputation: 3

MYSQL Where statement/clause being ignored

SELECT country.continent, country.name, city.district, city.name, city.population
FROM world.country, world.city
WHERE city.population < 3000000 
LIMIT 20, 30;

I'm getting a cartesian product instead of a table showing the contents of the select clause where the city population is over 3 million.

world is the name of the database country and city are tables

please help.

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

You need to join the tables together:

SELECT country.continent, country.name, city.district, city.name, city.population
FROM world.country JOIN
     world.city
     ON country.code = city.countrycode
WHERE city.population < 3000000 
LIMIT 20, 30;

As a hint for writing SQL: Never use commas in the from clause. Always use explicit joins. These are more powerful than implicit joins (and many people think they make queries easier to read and maintain).

Upvotes: 2

Related Questions