SELECTing a hierachy

  • I do not need the code to make the hierarchy. I have a table and want to know if there is a way to select an entire hierarchy from the table. Here's my example: We have districts and schools

    AccountName LevelIsCurrent
    District 110
        School 220
        School 321
    District 210
        School 420
        School 520

    If you look, you can see school 3 of district 1 is current, so I want to return all three rows (District 1, School 2 and School 3). But no one under District 2 is current, so they're not needed. I obviously know how to select rows where IsCurrent = 1, but what about District 1 and School 2 that are not?

    Does anyone have a clever solution?

    Thank you,

    Amy

  • How is the link established between School 3 and District 1. Do you have a column such as parent_district in the table?

  • george-178499 - Friday, March 30, 2018 9:42 PM

    How is the link established between School 3 and District 1. Do you have a column such as parent_district in the table?

    Hi Amy,

    I'll add that without the information the George pointed out above, this project is doomed to failure.  You must have or create a "parent" and "child" column to identity the structure of the hierarchy.  And, no... the order that something is inserted into a table is totally unreliable in relational databases.  You either need relational columns (Parent/Child in this case) or you need an ORDER BY in your queries.  Since that latter will fail to meet the requirements, in this case, you must add two columns to the mix to establish the relationship between items on different levels.

    Depending on how folks implemented the hierarchy for your table, there may only be one column that contains the "hierarchical path" or a couple of columns containing the left and right bowers for Nested Sets.  You need to do the research as to how the hierarchical structure is maintained and then include those columns in your post in order for us to help you sort this out.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)


  • AccountName    Level    IsCurrent      Parent

    District 1        1             0           District 1
    School 2          2             0           District 1
    School 3          2             1           District 1

    District 2        1             0           District 2

    School 4          2             0           District 2

    School 5          2             0           District 2
    School 5        2           0         District 2

    Every record has to have a parent as above or something similar to show how they are related at each levels with the parent.  It is better to build a hierarchy table that is built per requirements, We have something similar that we run a job every 24 hours to build this hierarchy table that support redistricting process approved by higher level management.

    =======================================================================

  • Apparently, the OP has left the building.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply