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

What is the version of SQL Server that you are using? You can use recursive CTEs in SQL Server 2005 to get your results. A generic solution on SQL Server 2000 requires some procedural code but you can expose it via non-inline table-valued function. Please specify your version and expected results. I am not sure how the concatenation is supposed to happen. Does this have to be in specific order for the related items.|||

This piece of code can get the result:

SELECT Y.id1 AS id, Y.minPid, text FROM codesubsections INNER JOIN

(SELECT Z.id1, COALESCE(Z.id4,Z.id3,Z.id2,Z.id1) AS minPid

FROM (SELECT A.id as id1, B.id as id2,C.id as id3, D.id as id4

FROM codesubsections AS A LEFT OUTER JOIN

codesubsections AS B ON B.id = A.CodeSubsectionID LEFT OUTER JOIN

codesubsections AS C ON C.id = B.CodeSubsectionID LEFT OUTER JOIN

codesubsections AS D ON D.id = C.CodeSubsectionID) AS Z) AS Y on Y.minPid=id

--You may keep adding the LEFT OUTER JOIN levels to move up.

|||

Sorry using 2000 and CTE not available.

The second solution would be fine and that is what I had done however, I don't know how deep I have to go beforehand, so I need to test the grandparent and if it has a code section and continue to loop. In order to do that I added an UnProcessed field and this is the loop that I set up and the UDF that I am using. The problem is that when the loop finishes that the bottom item's codesectionid is null and does not get included in the last loop so I never get the 5 text value to update the 6 and 7 value. it only goes to the 6 record. Hope this makes sense.

I really do appreciate the responses. Thanks in advance

DECLARE @.id INT
SET @.id = 1
WHILE @.id = 1

BEGIN
UPDATE CodeSectionsSummary SET
CodeSectionsSummary.UnProcessed = PCSUMMARY.UnProcessed,
CodeSectionsSummary.CodeSubSectionID =PCSUMMARY.CodeSubSectionID,
CodeSectionsSummary.CodeSection = PCSUMMARY.Parent,
CodeSectionsSummary.ParticipantTypeID = PCSUMMARY.ParticipantTypeID,
CodeSectionsSummary.CodeTypeID = PCSUMMARY.CodeTypeID
FROM
CodeSectionsSummary
INNER JOIN PCSUMMARY(0) PCSUMMARY ON CodeSectionsSummary.ID = PCSUMMARY.ID

IF EXISTS (SELECT COUNT(*) FROM CodeSectionsSummary WHERE UnProcessed = 1)
SET @.id = 0
ELSE
CONTINUE
END

Code for the UDF

CREATE FUNCTION dbo.PCSUMMARY
( @.CNT INT)

RETURNS @.RtnValue TABLE
(
[ID] INT ,
CodeSubSectionID INT,
Parent VARCHAR(10),
UnProcessed INT,
ParticipantTypeID INT,
CodeTypeID INT
)
AS
BEGIN
INSERT INTO @.RtnValue
SELECT CodeSectionsSummary.ID,
CodeSubsections_2.FormerCodeSubsectionID AS CodeSubSectionID,
COALESCE (CodeSections_2.CodeSection, CodeSections_1.CodeSection, CodeSections.CodeSection) AS Parent,
CASE TEST.FormerCodeSubsectionID
WHEN ISNULL(TEST.FormerCodeSubsectionID,0) THEN
CASE CodeSubsections_2.FormerCodeSubsectionID
WHEN isnull(CodeSubsections_2.FormerCodeSubsectionID, 0)
THEN 1 ELSE 0 END
ELSE 2
END AS UnProcessed,
COALESCE (CodeSubsections_2.ParticipantTypeID, CodeSubsections_1.ParticipantTypeID, CodeSubsections.ParticipantTypeID) AS ParticipantTypeID,
COALESCE (CodeSections_2.CodeTypeID, CodeSections_1.CodeTypeID, CodeSections.CodeTypeID) AS CodeTypeID

FROM CodeSectionsSummary INNER JOIN
CodeSubsections ON CodeSectionsSummary.ID = CodeSubsections.ID INNER JOIN
CodeSections ON CodeSubsections.CodeSectionID = CodeSections.ID AND
CodeSubsections.CodeSectionID = CodeSections.ID AND CodeSubsections.CodeSectionID = CodeSections.ID LEFT OUTER JOIN
CodeSections CodeSections_2 INNER JOIN
CodeSubsections CodeSubsections_2 ON CodeSections_2.ID = CodeSubsections_2.CodeSectionID LEFT OUTER JOIN
CodeSubsections TEST ON CodeSubsections_2.FormerCodeSubsectionID = TEST.ID RIGHT OUTER JOIN
CodeSubsections CodeSubsections_1 INNER JOIN
CodeSections CodeSections_1 ON CodeSubsections_1.CodeSectionID = CodeSections_1.ID AND
CodeSubsections_1.CodeSectionID = CodeSections_1.ID AND CodeSubsections_1.CodeSectionID = CodeSections_1.ID ON
CodeSubsections_2.ID = CodeSubsections_1.FormerCodeSubsectionID ON
CodeSubsections.FormerCodeSubsectionID = CodeSubsections_1.ID
WHERE (CodeSectionsSummary.CodeSubSectionID IS NOT NULL)
RETURN
END

|||

I once had a situation like yours...

Why don't you do this:

declare @.iSubSection as int,
@.vcText as varchar ( max ),
@.tbTable as table ( iID int )

set @.iSubSection = <<id of your starting section>>

while not @.iSubSection is null

begin
insert into @.tbTable
select @.iSubSection

select @.iID = ID , @.iSubSection = CodeSubSectionID , @.vcText = Text
From your_table
where @.iSubSection = id
end

update your_table
set text = @.vcText
from @.table
where iID = ID

|||

Miguelb, i think you are on to a possible solution to this nightmare. Business Analyst that use reverse logic had a special place in hades reserved. I played around with your suggestion. The problem is that in the following records 7 looks at 6, 6 at 6 and 5 never gets evaluated because of the null value. Note, the sample data does not show that the text for 5 will probably be different than 6, sorry about that. I will keep playing with this, most promising approach so far.

5 NULL 16:1 B

6 5 16:1 B

7 6 17 B p2

|||

Hi,

I read a few papers about this topic. I came across this solution by using "String-Based "Nested Sets" technique" from the following link. Here is the link to the resource by Dennis W. Forbes.

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies2.htm

I tested your dataset with this solution and it worked pretty good.

|||

Ever since Heracles snatched Cerberus that they let anyone enter in hades, so I wouldn't be surprised to see those people there :)

You are right - I forgot the last leaf. Try this (you may have to tweak it a little more):

declare @.iID as int,

@.iSubSection as int,

@.vcText as varchar ( max ),

@.tbTable as table ( iID int )

set @.iSubSection = <<id of your starting section>>

set @.iID = null

while not @.iSubSection is null

begin

insert into @.tbTable

select @.iSubSection

select @.iID = ID , @.iSubSection = CodeSubSectionID , @.vcText = Text

From your_table

where @.iSubSection = id

end

if @.iID is not null

begin

insert into @.tbTable

values ( @.iID )

end

update your_table

set text = @.vcText

from @.table

where iID = ID