Help - Unusual Problem

First, I can not change the data structure. I have a table called codesubsections that has an identity as the primary key. When a new codesubsection is entered the user can mark a formercodesubsection as a parent. The problem is that I need to be able to evaluate the table and identify the lowest CodeSubsectionID for a given row. so for instant when I look at row 7 it's text is 17 B p2 but because the CodeSubSectionID is not null I need to look at 6 then 5 and since 5 is null I need the text for 5,6,7 all to be 16:1 B. The only solution I have had any kind of luck with is to do a self join 3 times and coalesce the values up. See the code at the end. I am at my wits end, I had it working the other way assuming that the CodeSubSectionID was the parent of the record. This is the final piece of code on a project that is supposed to end tomorrow.

Any help is appreciated. I would normally post the procedures but I think that they are more confusing that the data explanation. This type of relationship is a first for me after 20 some years.





ID CodeSubSectionID Text

1 NULL 16:1 A

2 NULL 16:1 B

3 NULL 17 A

4 NULL 17 B p1

5 NULL 16:1 B

6 5 16:1 B

7 6 17 B p2

8 3 17 C

9 NULL 18