nested lookup - query to get the respective child(s)

[Manhao Chen]

hi all,

i have 2 tables which are called

- tbl_area
- tbl_area_lookup

my data are to be structured as each area could have many sub area and so forth...

so imagine at the top level which will be

Many Region -> Many State -> Many Cities - > Many District -> Many Suburbs - > Many Towns

so in terms of data it will look something like this, all of the region will be stored into 1 table...

tbl_area
PK | Area
1 | West Coast
2 | My State
3 | Your State
4 | Our State
5 | My City
6 | Your City
7 | Our City
8 | My District
9 | Your District
10 | Our District
11 | My Town
12 | Your Town
13 | Our Town

the lookup will be like

tbl_area_lookup
PK | Parent | Child |
1 | 1 | 2 |
2 | 1 | 3 |
3 | 1 | 4 |
4 | 2 | 5 |
5 | 2 | 6 |
6 | 3 | 7 |
7 | 5 | 8 |
8 | 8 | 11 |
9 | 8 | 13 |

what i'm after is a query that can search the lookup table that will get all the childs when i pass a parent

eg... if i query for parent say... 2 My State

i would expect all of its Cities - > District -> Suburbs - > Towns be returned

the idea is at any given point, i pass in the area id in the lookup will find all its respective childs



Edited 1 time(s). Last edit at 09/11/2012 10:04PM by Manhao Chen.