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.
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 :
select cityname, count(distinct Country_ISO)
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.
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
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...