0

I have got a table which goes something like this: 

State_id | County_id|  City_id |     Name      |    Additional_Name
-----------------------------------------------------------------
   1     |     0    |     0    |   California  |        State
   1     |     1    |     0    |   Los Angeles |        County
   1     |     1    |     1    |   Los Angeles |        City
   1     |     2    |     0    |   San Diego   |        County
   1     |     2    |     1    |   San Diego   |        City
   2     |     0    |     0    |   Texas       |        State
   2     |     1    |     0    |   Harris      |        County
   2     |     1    |     1    |   Houston     |        City

It goes on for 10,000 rows. What I'm trying to accomplish is to build a SELECT statement which will result in:

  State       |   County     |     City
 -------------------------------------------
  California  |  Los Angeles |    Los Angeles
  California  |  San Diego   |    San Diego
  Texas       |  Harris      |    Houston

As you can see i want to select every city and display it's state, and county. The state_id, county_id, city_id and Additonal_Name columns should be essential in solving this problem, but i have no idea how to use them.

Improve this question
1
  • if possible, separate that information into three tables. That is a poorly designed database, and will only cause you more headaches in the future. This would fall under 'Normalizing' a database. Commented May 13, 2014 at 19:41

1 Answer 1

Reset to default
2

This will get your data, using your current table structure:

SELECT t2.[Name] AS [State]
    ,t3.[Name] AS County
    ,t1.[Name] AS City
FROM MyTable t1
JOIN MyTable t2 -- get state
    ON t2.State_id = t1.State_id
       AND t2.County_id = 0
       AND t2.City_id = 0
JOIN MyTable t3 -- get county
    ON t3.County_id = t1.County_id
       AND t3.State_id = t1.State_id
       AND t3.City_id = 0
WHERE t1.City_id > 0 --(or use t1.Additional_Name = 'City'

But Really:

You should be normalizing your database into three separate tables:

  1. City
  2. State
  3. County

This will make using your data, and writing queries - muchh simpler and readable.

The tables would look something like this:

State

ID Name
 1 California
 2 Texas

County

ID Name
 1 Los Angeles
 2 San Diego
 3 Harris

City

ID StateID CountyID Name
 1 1       1        Los Angeles
 2 1       2        San Diego
 3 2       3        Houston

Hopefully you can see how much easier that is to manage things. You can choose to add a StateID to the County table to normalize further. But I'll keep my answer short.

Select the same data from these new tables with a similar query:

SELECT state.[Name] AS [State]
    ,county.[Name] AS County
    ,city.[Name] AS City
FROM MyCity city
JOIN MyState state ON state.ID = city.StateID -- get state
JOIN MyCounty county ON county.ID = city.CountyID -- get county
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I queried the data sample i showed in the topic and got 42 records, while i should get 3 (as there are 3 cities). There were all kinds of data in State column, like Los Angeles, Harris, Houston etc. Thank you for all the normalization advices and I probably will get to it eventually, but for now the form of the table must stay as it is.
This time i get: 'California Los Angeles Los Angeles' Texas Los Angeles Houston California Los Angeles Los Angeles Texas Los Angeles Houston California San Diego San Diego California San Diego San Diego California Harris Los Angeles Texas Harris Houston California Houston Los Angeles Texas Houston Houston' '/code'
I see you reuse ID values for county, if the state is different. Again, this table structure is bad for maintenance and querying. edited post to work
Now it works perfectly. Thank you so much for your help and time consumed. I'm aware that this table needs normalizing and "splitting" into three tables, but it was a thing i had to do with this form. Thank you again and have a great day!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.