Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    39

    Unanswered: How can I check cardinalities

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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

  4. #4
    Join Date
    Sep 2004
    Posts
    39
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •