How can I check cardinalities

JohnStrecker · #1 (**permalink**) 11-05-04, 04:08

Hi!

I am not even sure "cardinality" is the correct name...

I have a "Cities" table with a "Country_ISO" field and a "Countries" table with a "Country_ISO" field as well. How can I (as easily as possible) check that there is indeed ONE and ONE ONLY country for each City.

Looking forward to your inspired guidance

Have great day

John in sunny Brussels

r937 · #2 (**permalink**) 11-05-04, 04:20

you can do this by selecting those cities which have more than one country, and seeing if any results come back

Code:

select cityname, count(distinct Country_ISO)
  from Cities
group by cityname
having count(distinct Country_ISO) > 1

RBARAER · #3 (**permalink**) 11-05-04, 10:44

Hello John,

You can add "or count(distinct Country_ISO) = 0" or replace "count(distinct Country_ISO) > 1" by "count(distinct Country_ISO) <> 1" (count() will not be negative) to Rudy's query to also get cities that are related to no country :

Code:

select cityname, count(distinct Country_ISO)
  from Cities
group by cityname
having count(distinct Country_ISO) <> 1

If you get no result, then each city is well related to one and only one country.

Regards,

RBARAER

JohnStrecker · #4 (**permalink**) 11-05-04, 18:44

Thank you but I have a little doubt: I don't see the "countries" table in your code. I believe the code you gave me will indeed tell me which CITY in the "citites" table have no - or too many - Country_ISO code

but

will it tell me which cities - throught their Country_ISO code - have NO CORRESPONDING record in the "countries" code (which is what I am after)

Say for instance I have "London" with country_ISO = "UK" but in the "countries" table the Country_ISO for Great Britain is "GB"... How do I find out that London has no corresponding "country"?

I am asking because I have had the case, using ISO codes for the cities and HSBC's home code for the countries ... so I have got to check them all...

Thank you for your interest

John in Brussels late at night.

r937 · #5 (**permalink**) 11-05-04, 18:58

oh, that's easy

Code:

select Cities.cityname
     , Cities.Country_ISO
  from Cities
left outer
  join Countries
    on Cities.Country_ISO
     = Countries.Country_ISO  
 where Countries.Country_ISO is null

Internet Services

Web Development

Digital Marketing

Consumer Tech