I have table something like this:
childId | parentId
1 | null
2 | 1
3 | null
4 | 2
Column childId is primary key of this table and parentId is foreign key to same this table and have reference to column (childId).
And I need to call a function and send parameter (childId) and function will find the most parent row of this child.
Example: If I pass childId = 4, the output result need to be 1.
Is there any solution for this problem?
EDIT:
I need something like hierarchy top level row. I have tried with recursive CTE but I couldn't get done.
It looks like a recursive CTE (common-table expression) is a good fit for this type of query.
Sample data
DECLARE @T TABLE (childId int, parentId int);
INSERT INTO @T VALUES
( 1 , null),
( 2 , 1 ),
( 3 , null),
( 4 , 2 );
Query
Replace constant 4
with a parameter. I'm including AnchorChildID
and AnchorParentID
to make it easier to understand the result and what is going on.
Run this query without the final filter WHERE ParentID IS NULL
to see how it works.
WITH
CTE
AS
(
SELECT
childId AS AnchorChildID
,parentId AS AnchorParentID
,childId AS ChildID
,parentId AS ParentID
FROM @T AS T
WHERE childId = 4
UNION ALL
SELECT
CTE.AnchorChildID
,CTE.AnchorParentID
,T.ChildID
,T.ParentID
FROM
CTE
INNER JOIN @T AS T ON T.ChildID = CTE.ParentID
)
SELECT ChildID
FROM CTE
WHERE ParentID IS NULL
OPTION(MAXRECURSION 0)
;
Result
ChildID
1