Countries & Cities Tables

I need to have tables of world countries and cities to use in a user registration form, I searched the Internet for scripts,xml,excel files for countries & cities lists but I couldn't find. Can anybody tell me where to get them from?

Hello my friend,

Let me introduce you to a website I used to worked on that has plenty of this information: -

http://www.wshtravel.com/travel_guides

Kind regards

Scotty

|||

Thanks but the link is not working

|||

I think that if you will try to do something like this on your page it will be very slow and very big, so maybe just use countries. You can try to get countries fromwww.usps.com just try to send pckage to international destination and you will get drop down list with countries to which USPS delivers packages. You can also trywww.UPS.com maybe they have also cites?

Thanks

JPazgier

Counting total number of items in each category

Hello everyone,

I have 2 tables. One with a list of countries and another with a list of users. The users table stores the Country they are from as well as other info.

Eg the structure is a little bit like this...

Countries:

--

CountryId

CountryName

Users:

UserId

UserName

CountryId

So, the question is how to a list all my countries with total users in each. Eg, so the results are something like this......

CountryName TotalUsers

United Kingdom 334

United States 1212

France 433

Spain 0

Any help woulld be great as I have been fumbling with this all morning. Im not 100% with SQL yet!!

Cheer

Stephen

here You go...

Code Snippet

Create Table #countries (

[CountryId] int ,

[CountryName] Varchar(100)

);

Insert Into #countries Values('1','USA');

Insert Into #countries Values('2','UK');

Insert Into #countries Values('3','IN');

Create Table #users (

[UserId] int ,

[UserName] Varchar(100) ,

[CountryId] int

);

Insert Into #users Values('1','John','1');

Insert Into #users Values('2','Dale','1');

Insert Into #users Values('3','Thome','2');

--With ZERO COUNT

Select

[CountryName],

Count([UserId])

from

#countries C

left Outer Join #users U on C.[CountryId] = U.[CountryId]

Group By

[CountryName]

--Without ZERO COUNT

Select

[CountryName],

Count([UserId])

from

#countries C

Inner Join #users U on C.[CountryId] = U.[CountryId]

Group By

[CountryName]

|||

Super! Many thanks and thank you for replying so quickly.

Makes sense now - easier than what I was trying to do!!!