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

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?

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

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

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