Country, state, city database free download. IP2LocationLookupTool Allow you to embed this Java component into your project for IP geolocation lookup. Cities Mysql/Microsoft Sql Database (150,000 records). Ajax Php Jquery Script. Download States Countries Mysql Sql Database Download. We Provide Cities / States / Countries database in SQL / Mysql format with Latitude Longitude for use in your applications.

Us City Database

Because countries can have multiple states and each state can have multiple cities when you join these 1 to many and 1 to many many your state count is inflated. So you need the distinct count of state. The city count is already unique to country and state, thus doesn't need the distinct. Where as state is not unique to country city, thus distinct is needed. This of course assumes you want the count of unique states in each country. SELECT c.name, count(distinct s.name) as statecount, count(Ci.name) as CityCount FROM countries c INNER JOIN states s on c.id = s.country_ID INNER JOIN cities ci ON s.id = ci.state_id GROUP BY C.name Or keeping your old style join notation: SELECT c.name, count(distinct s.name) as statecount, count(ci.name) citycount FROM countries c,states s,cities ci WHERE ci.state_id = s.id and s.country_id = c.id GROUP BY s.name Consider the following example: or pictorially below See when the joins occur between country, state and city. State gets repeated because of the join to city, making state no longer unique in that column, by doing a distinct we only return a count of 2 states instead of 7, one for each record.

+-----+------------+-------------+ USA Illinois Chicago USA Illinois Springfield USA Illinois Peoria USA California LosAngeles USA California Sacramento USA California SanDeigo USA California Hollywood USA California Oakland ----- ------------ ------------- Name statecount Citycount USA 2 7 . The two 1-M table joins MAKES distinct matter regardless of no duplicates in the data. Consider the example: if you don't use distinct state count and city count will match. If that's what you want then you really don't need to count state. Notice two different queries one w/ distinct one w/o.

And the results are different. So if you don't need the distinct, then you're saying count the state once for each record of a city. If you have the distinct then you're saying return the count of the unique state records for the country. – Aug 4 '17 at 16:58.

I need help designing my country, city, state tables. I will provide sample data from my table so that you can help me better on my problem. This is my country table: Country ______ code name US United States SG Singapore GB United Kingdom This is my city table: City _____ id country city state 1 US Birmingham Alabama 2 US Auburn Alabama.. 29 GB Cambridge NULL 30 GB Devon NULL My problem is that the only country that has the state field is the US. All other cities have a null value. My temporary solution for this is to just create a special city table for the United States, then all other countries have another city table that doesn't have the state field. I think this will just complicate the matter, because I have two tables for cities.

How can I improve this design? There are lots of countries besides the United States that have political divisions between the national and municipal level. Australia has states, Canada has provinces, Japan has Prefectures, and so forth. Bs 5839 Part 1 2002 Pdf Files. The question is how do you track this information and keep it consistent? Download Aur Aahista By Pankaj Udhas here. You could have a 'dummy record' at the middle level for countries that don't have one. Another way to handle this is to denormalize foreign keys to all levels down to the entity containing the address. If country and city are mandatory then their foreign keys would be not nullable whereas your state FK could be nullable.

If you go the denormalization route, you will need application logic to ensure that your foreign keys are consistent with each other. If you go the dummy state record route, you will need application logic to ensure that dummy layers are hidden from users in the user interface.