SELECTing a hierachy

Question

Post reply

SELECTing a hierachy

Amy.G

SSCrazy

Points: 2305
More actions
March 30, 2018 at 3:28 pm

#410378

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 Level IsCurrent
District 1 1 0
    School 2 2 0
    School 3 2 1
District 2 1 0
    School 4 2 0
    School 5 2 0
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

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

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

george_at_sql SSCommitted Points: 1967 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

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)

Emperor100 SSCrazy Points: 2173 More actions · Answer 3

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.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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)

AccountName	Level	IsCurrent
District 1	1	0
School 2	2	0
School 3	2	1
District 2	1	0
School 4	2	0
School 5	2	0