1

Interestingly none of the below questions helped me:
How to organize country/state/city browsing in MVC
City Country State Database
Country/City/state validation
Country/State/City Database?
How to do a city/state/country code lookup based on zip/country input by the user?
City, State, Zip, Country list/script for sql server table

I looked at a question about database design which is somehow helpful, but there are some questions that have been left unanswered. I have 3 tables as below: 

Country ( CountryID, CountryCode, CountryName )
Region  ( RegionID, RegionCode, RegionName, CountryID )
City    ( CityID, CityCode, CityName, RegionID )

I have CityID in user's table. The question is what if user does not have state? How should I use dropdowns in my case?
Using ajax based textboxes is out of question in my case.

Community
  • 1
  • 1
Alireza
  • 6,497
  • 13
  • 59
  • 132

1 Answers1

0

Are you trying to reach the list of countries based on cityID if so

SELECT Country.* FROM Country
LEFT JOIN Region on Region.CountryID = Country.CountryID
LEFT JOIN city on Region.RegionID = city.RegionID
WHERE city.CityID=44

would produce the country for the city. Is this what you are looking for?

The resulting country could be used to set the selected country in a dropdown.

Running a second query for regions using CityID and using the result to auto select the correct region for the city can be achieved too.

Ekim
  • 1,105
  • 11
  • 28
  • 1
    No, First this is very very inefficient way to accomplish the job as your query fetch all the possible records from all 3 tables. The user should select a country then a state and at last a city. The above query is useless because no one list all the possible cities in the world in one dropdown and based on that select proper state. To be brief `Country->State->City`. Here some countries don't have city? How to solve this problem? – Alireza Aug 04 '12 at 07:07
  • Ok, I understood the question as you only have cityID set already to work with and need to go backwards. loading the content for the other comboboxs from that ID. Can you use ajax for drop downs? – Ekim Aug 04 '12 at 07:37