Member Avatar for jeeter19

Hi,

I am practicing MySQL JOINs and was hoping I could get some insight on how to do a particular problem.

I am using the World database, it has a table called City with name, population, country code. It also has a table called Country, with Code and Name.

What I am trying to do is, SELECT all the cities for a country IF AT LEAST ONE city in that country has a population of 5,000,000 or more. So far I've only been able to get the Cities that have a population of 5,000,000 or more.
So in other words, if one city in a country has 5,000,000 or more population, get all the cities for that country.

I hope this makes sense. Any guidance to tutorials or documentation on how to do these types of JOINs would be great.

Thank you

  • 2 Contributors
  • 2 Replies
  • 95 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by jeeter19

Recommended Answers

All 2 Replies

Member Avatar for tesuji

Hi

You already have got this:

select name, country_code from city where population >= 5000000);

If you omit "name", you get a country_code list with all countries having at least one city with pop. >= 5000000.

select distinct country_code from city where population >= 5000000);

This list can be used to get all cities of a particulary country where the condition is true:

select "Name" from city where country_code in (select distinct country_code from city where population >= 5000000);

You may add further country data by inner join:

select city."Name", country."Name" from city join country on city.country_code = country.country_code
  where city.country_code in (select distinct country_code from city where population >= 5000000);

You may omit or replace delimeters " " by mysql ` `. For "Name" is a reserved word in most database systems it isn't a good idea to use it for a column. In such case some database require that the word be surrounded with delimeters.

There is also an if-statement (if (cond) then ... else... end if; ) what can be used in user definded functions written in PSM language. This is supported by mysql. Unfortunately, mysql does not support the ANSI SQL if-statement for select-statements, yet it has case-statement.

-- tesu

Edited by tesuji because: sopytypos
Member Avatar for jeeter19

Thank you, I appreciate you taking the time to answer my question in full detail! I'll be sure to study up on DISTINCT and more about JOINs :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.