none
State County City Problem RRS feed

  • Question

  • Im dealing with address data, and based on the schema I have, I can't query to answer the following question: 'What county(s) is Birmingham, AL in?'

     

    Here is what I have:

     

    State2 table:

    StateID (Identity, primary key),

    Name (full state name)

    TwoLetterCode ('AL')

     

    County2

    CountyID (Identity, primary key),

    Name (full county name),

    StateID (from the State2 table)

     

    City

    CityID (Identity, primary key)

    Name (full city name),

    StateID (from the State2 table)

     

    Foreign keys:

    County2.StateID to State2.StateID

    City.StateID to State2.StateID

     

    It so happens that Birmingham, AL is in two counties  (so cities can cross county lines). In our data, there is one CityID for each StateID. Does the countyID need to be in the City table?

     

    What do I need to be able to determine what county(s) that a city resides in?

     

    Thank you for your help!

     

    cdun2

    Thursday, September 4, 2008 4:31 PM

All replies

  • To make this possible, countyid and cityid has to exist in the same table. You need countyid in the city table to join with the county table to get the county name.

    Thursday, September 4, 2008 4:59 PM
  • I think you would need to add another table and change the relationships a bit.

     

    Cities (PK CityId)

     

    Counties (PK CountyId) (Has StateId AS FK)

     

    States (PK StateId)

     

    CountyCities (PK CountyId, CityId)

     

    This way a city can be in multiple counties, and multiple counties can be in a state.

     

     

     

     

    Thursday, September 4, 2008 5:11 PM
  • Does this work?

     

    Code Snippet

    select c.name

    from State2 s
    join County2 c on s.StateID = c.StateID
    join City y on y.StateID = s.StateID
    and c.StateID = y.StateID
    where s.TwoLetterCode = 'AL'
    and y.name = 'Birmingham'

     

     

    Denis The SQL Menace

    SQL Server Programming Hacks

    ASP.NET hacks

    Thursday, September 4, 2008 5:14 PM
    Moderator
  • Denis,

     

    I think that would return all counties in the State....

     

    Code Snippet

    CREATE TABLE State2

    (

    StateId INT

    ,TwoLetterCode CHAR(2)

    )

    GO

    CREATE TABLE County2

    (

    CountyId INT

    ,name varchar(20)

    ,StateId INT

    )

    CREATE TABLE City

    (

    name varchar(20)

    ,StateId INT

    )

    Go

    INSERT INTO State2 VALUES (1, 'AL')

    INSERT INTO State2 VALUES (2, 'NJ')

    INSERT INTO County2 VALUES (1,'County1',1)

    INSERT INTO County2 VALUES (2,'County2',1)

    INSERT INTO County2 VALUES (3,'County3',1)

    INSERT INTO County2 VALUES (4,'County4',1)

    INSERT INTO County2 VALUES (5,'Middlesex',2)

    INSERT INTO County2 VALUES (6,'Ocean',2)

    INSERT INTO City VALUES('Birmingham',1)

    INSERT INTO City VALUES('Some other City',1)

    INSERT INTO City VALUES('Trenton',2)

     

     

     

    select c.name

    from State2 s

    join County2 c on s.StateID = c.StateID

    join City y on y.StateID = s.StateID

    and c.StateID = y.StateID

    where s.TwoLetterCode = 'AL'

    and y.name = 'Birmingham'

    drop table state2

    drop table county2

    drop table city

     

     

     

    Jay
    Thursday, September 4, 2008 5:30 PM
  • Yep, you are right, my bad  :-(

     

     

    Denis The SQL Menace

    SQL Server Programming Hacks

    ASP.NET hacks

     

    Thursday, September 4, 2008 5:36 PM
    Moderator
  • Thanks for your input. I'll try something with the City table and get back to you.

     

    cdun2

     

    Thursday, September 4, 2008 5:54 PM
  • Really, your best bet is to track things by zip code.  Go to USPS and either 1. Write a spider to get all the zips there are or 2. Buy a zipcode database.

     

    That way, in your address table, you do NOT store City or State...only ZIP.

     

    In the Zip table you'll have information on the City and the County.

     

    90210 is ALWAYS "Beverly Hills" city

    90210 is ALWAYS "Los Angeles" county

     

    "Los Angeles" county is ALWAYS "California" State

     

    The USPS has an "Official" name for every possible Zip code (there are only 100000 potential zip codes, 00000-99999) so it's a pretty small table in database terms.

     

    If you want to get fancy, you can do one-to-many relationship for Zip to City:

    91602 is recognized by USPS as "North Hollywood" official, but may also be called:

    West Toluca Lake

    Valley Village

     

    However, limiting the selection to ONLY the "official" usps name will not confuse anyone.  Everyone living in 91602 KNOWS they're in "North Hollywood" and they know that it might also be called by other names.

     

    Cheers

     

     

    Thursday, September 4, 2008 7:36 PM