March 30, 2018 at 3:28 pm
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
March 30, 2018 at 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?
March 31, 2018 at 8:54 am
george-178499 - Friday, March 30, 2018 9:42 PMHow 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
Change is inevitable... Change for the better is not.
April 7, 2018 at 2:13 pm
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.
=======================================================================
April 7, 2018 at 5:54 pm
Apparently, the OP has left the building.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply