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

  • Question

  • User-1112204889 posted

    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

    Wednesday, September 12, 2012 5:51 PM

Answers

  • User269602965 posted
    Table1 has self-referencing hierarchical values
     
    id   Reporting_id
    A1    A
    A2    A
    A11  A1
    A12  A2
    B1    B      
    
    Select the details for Reporting_id = "A" without START WITH CONNECT BY clause 
    
    Select * from Table1 where Reporting_id ="A"
     
     
    id  Reporting_id
    A1  A
    A2  A
     
    
    Using START WITH CONNECT BY clause
     
    Select * from Table1 START WITH Reporting_id ="A" CONNECT BY id=Reporting_id;
     
    id   Reporting_id
    A1    A
    A2    A
    A11  A1
    A12  A2
     
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 12, 2012 10:33 PM

All replies

  • User269602965 posted

    for self-referencing hierarchical query

    use START WITH and CONNECT BY clauses if supported by your database query language


    Wednesday, September 12, 2012 7:05 PM
  • User-1112204889 posted

    do you have a sample i could look at?

    Wednesday, September 12, 2012 7:17 PM
  • User269602965 posted
    Table1 has self-referencing hierarchical values
     
    id   Reporting_id
    A1    A
    A2    A
    A11  A1
    A12  A2
    B1    B      
    
    Select the details for Reporting_id = "A" without START WITH CONNECT BY clause 
    
    Select * from Table1 where Reporting_id ="A"
     
     
    id  Reporting_id
    A1  A
    A2  A
     
    
    Using START WITH CONNECT BY clause
     
    Select * from Table1 START WITH Reporting_id ="A" CONNECT BY id=Reporting_id;
     
    id   Reporting_id
    A1    A
    A2    A
    A11  A1
    A12  A2
     
    
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 12, 2012 10:33 PM
  • User-578610739 posted

    Hi Friend,

    see this,

    http://www.sitepoint.com/forums/showthread.php?102205-Nested-%28Hierarchical%29-Menus-w-Oracle-and-PHP

    Friday, September 14, 2012 12:45 AM