An Example of poorly designed SQL Tables
Here is what the table looks like:
StateID State
1 TX:Texas
2 TX:Dallas
3 TX:Houston
4 CA:California
5 CA:Fontana
6 CA:Riverside
Here is the method I proscribed to him...
- rename the tblState to tblStateHidden
- go into tblStateHidden design view
- make the third field called "Region", char / 50
- create a field called "Parent", int
- run an update against the entire table (or do it manually) to move the city name or whatever into the Region field... so you will end up with something like this...
UPDATE tblStateHidden
SET State = SUBSTRING(State, 1, 2), Region = SUBSTRING(State, 4, LEN(State))
StateID State Region Parent
1 TX Texas -null-
2 TX Dallas -null-
3 TX Houston -null-
4 CA California -null-
5 CA Fontana -null-
6 CA Riverside -null-
- Now, you will need to do this manually... Go thru the table and get the entries where the Region is the name of the State. Set the Parent field for these entries equal to the StateID field of that entry.
StateID State Region Parent
1 TX Texas 1
2 TX Dallas -null-
3 TX Houston -null-
4 CA California 4
5 CA Fontana -null-
6 CA Riverside -null-
UPDATE: Here is a query that can perform this next part in a single fell swoop. Thanks to James Curran (www.noveltheory.com) for this info.
UPDATE tblStateHidden
SET Parent = s2.StateID
FROM tblStateHidden s1,
(select StateID, State from tblStateHidden WHERE StateID = parent) s2
WHERE s1.State = s2.State
- Now.... here is the fun part... you need to run another update against the entire table to set the Parent = to the StateID of the State it belongs in.... I dont know the best way to do this... the long way would be to run this query independantly 50 times for the 50 states:
UPDATE tblStateHidden
SET Parent = x
WHERE State = 'y'
x = StateID of the State you are setting
y = State value of State you are setting
so you will get this:
StateID State Region Parent
1 TX Texas 1
2 TX Dallas 1
3 TX Houston 1
4 CA California 4
5 CA Fontana 4
6 CA Riverside 4
- Then go thru and find all the "Parents" (IE, StateID = 1 in the example) and set their Parent = 0. Now the Table should look something like this:
StateID State Region Parent
1 TX Texas 0
2 TX Dallas 1
3 TX Houston 1
4 CA California 0
5 CA Fontana 4
6 CA Riverside 4
Now, build a view called tblState and have it set with the following Query:
SELECT StateID, (State + ': ' + Region) AS State FROM tblStateHidden
This will make a view that is identical to the old tblState... when someone calls
a query on tblState, they will get this view... this will prevent other pages from
breaking because tblState doesnt exist anymore. NOTE: I put a space after the :
in that SELECT statement... if there wasnt a space in the current table, then remove
that space (I did not know whether or not there was a space in the original table).
Now... you can do a select query where Parent = 0 to get a listing of just the
States (or Parents)... also, you can build a dropdown list of the entire table
(order by State, Parent to keep it in good order... it will put the parent first
since it's parent is 0, all the others have something after 0).
The dropdown list will have the value of the StateID. After a person selects an
item in the dropdown and submits, etc... the asp code can check to see if the
selected state/region has a parent of 0. If so, then it must select the entire
state and you can do so by querying for all items with the parent of the
currently selected StateID.