CODEWITHSUNDEEP
sql
paz333
paz333

Reputation: 1

Making an SQL query that finds All pairs of cities located in the same country

I have a table city with the attributes name and country and I have to write an sql query that outputs all cities with the same country. So things like Tokyo | Osaka and Osaka | Tokyo I have tried several different conditions at the end of SELECT c.name, c.country FROM city c WHERE ... but I just cant seem to get it.

Upvotes: 0

Views: 1437

Answers (1)

The Impaler
The Impaler

Reputation: 48810

This is a very simple self join. Should look something like:

select
  a.country, 
  a.name as city1, 
  b.name as city2
from city a 
join city b on a.country = b.country and a.name < b.name

Notice that the table city shows up twice (with different aliases) so you can pair cities. The ON clause specifies how you want to pair them: "same country, different names".

Also, notice I compared using < to ensure that each pair doesn't show up twice (anverse + reverse). If you want both, just use <> instead.

Upvotes: 1

Related Questions