Recursive hirarchal lookups

[mcgrcoAgain]

Hi,

Im new to sql server so please forgive me if this question is obvious.
Any help is appreciative

what I'm trying to do is a recursive look up of child to ultimate parent.

The data is structured like so . if I look up child 2-4 I want to return sub Parent 2

child sub Parent Ultimate Parent
1
2 2 1
3 2 1
4 2 1


--**code as follows. this only returns the first record

DECLARE @CHILD_PROFIT_CENTRE varchar(20)

SET @CHILD_PROFIT_CENTRE = 'SIH' --ultimate parent code

create table #CTE_Example
(
CHILD_PROFIT_CENTRE varchar(20)
,CHILD_PROFIT_CENTRE_DESC varchar(100)
,PARENT_PROFIT_CENTRE varchar(100)
,Depth float
)

insert into #CTE_Example (CHILD_PROFIT_CENTRE, CHILD_PROFIT_CENTRE_DESC, PARENT_PROFIT_CENTRE, Depth)


SELECT A.CHILD_PROFIT_CENTRE, A.CHILD_PROFIT_CENTRE_DESC, A.PARENT_PROFIT_CENTRE, 0 AS Depth
FROM STAGING_SIH..SIHFEEDS_SAP_PCHIERARCHY A
WHERE A.CHILD_PROFIT_CENTRE = @CHILD_PROFIT_CENTRE
UNION ALL
SELECT A.CHILD_PROFIT_CENTRE, A.CHILD_PROFIT_CENTRE_DESC, A.PARENT_PROFIT_CENTRE, #CTE_Example.Depth + 1 AS Depth FROM STAGING_SIH..SIHFEEDS_SAP_PCHIERARCHY A
JOIN #CTE_Example ON A.PARENT_PROFIT_CENTRE= #CTE_Example.PARENT_PROFIT_CENTRE


SELECT * FROM #CTE_Example

 

[Pauldohert]

This has been here for a few days - and noones replied - so I will maybe help (maybe not) by pointing out that I am not clear on what results you want?

 

[dan-cat]

You want to look-up the parent value of the relevant child?

SELECT a.SubParent
FROM #CTE_Example a
WHERE a.Child = @myChildValue

 

[WayneRyan]

mcgrcoAgain ,

It doesn't have to be recursive, just traverse a linked list.

I don't have SQL Server today, so this is pretty rough:

CREATE PROCEDURE GetParent(@CHILD_PROFIT_CENTRE Varchar(20))
AS 

Declare @CHILD  Varchar(20)
Declare @PARENT Varchar(20)
Declare @CHILD_DESC Varchar(100)
Declare @Depth Int

Declare @SearchStatus Varchar(20)

create table #CTE_Example
  (CHILD_PROFIT_CENTRE varchar(20),
   CHILD_PROFIT_CENTRE_DESC varchar(100),
   PARENT_PROFIT_CENTRE varchar(100)
   Depth float)

Set @SearchStatus = 'Searching'
Set @Child = @CHILD_PROFIT_CENTRE
Set @Depth = 0

While @SearchStatus = 'Searching'
   Begin
      --
      -- Get the Parent for the current child.
      --
      Select @Parent = SubParent
      From   STAGING_SIH..SIHFEEDS_SAP_PCHIERARCHY
      Where  Child = @Child
      --
      -- If no rows, this is the ultimate parent.
      --
      If @@RowCount = 0 Then
         Begin
            Set @SearchStatus = 'Found'
         End
     Else
        --
        -- Log the subparent and increment depth
        --
        insert into #CTE_Example (CHILD_PROFIT_CENTRE, CHILD_PROFIT_CENTRE_DESC, PARENT_PROFIT_CENTRE, Depth)
        Values(@Child, '', @Parent, @Depth)
        Set @Depth = @Depth + 1
        Set @Child = @Parent
    End If
   End

SELECT * FROM #CTE_Example

Wayne