I have a table with hierarchical data in it, the structure goes like this:
ID ParentId
---- ----------
1 NULL
2 1
3 2
4 2
5 3
6 5
If I pass the node Id I would like to get the top most node Id/details by traversing through all its parents in SQL.
I tried CTE, i somehow cannot get the combination correct. However, i got this working as a function but it is so slow that i had to post this question.
In the above example if I pass 6, i would want to have the top most i.e. 1. By traversing through 6 => 5 => 3 => 2 => [1] (result)
Thanks in advance for your help.
To find out who that child's parent is, you have to look at the column parent_id , find the same ID number in the id column, and look in that row for the parent's name. In other words, Jim Cliffy has no parents in this table; the value in his parent_id column is NULL .
A common way to do this in SQL Server is to use a Recursive CTE: Here's your test data as an insert: CREATE TABLE #yourmom ( custid INT, custname VARCHAR(10), deptid VARCHAR(3), company VARCHAR(10), parentcustid INT, enrolled BIT ) INSERT #yourmom ( custid, custname, deptid, company, parentcustid, enrolled ) SELECT x.
parent = c. child ) SELECT distinct parent, child , level FROM cte order by level, parent; This will give you all descendants and the level.
A parent-child relationship between two tables can be created only when there is a PRIMARY KEY in one table and FOREIGN KEY in another table. Syntax diagram - SQL JOIN of three tables.
DECLARE @id INT = 6
;WITH parent AS
(
SELECT id, parentId, 1 AS [level] from tbl WHERE id = @id
UNION ALL
SELECT t.id, t.parentId, [level] + 1 FROM parent
INNER JOIN tbl t ON t.id = parent.parentid
)
SELECT TOP 1 id FROM parent ORDER BY [level] DESC
@TechDo's answer assumes the lowest ID will be the parent. If you don't want to rely on this then the above query will sort by the depth.
Please try:
declare @id int=6
;WITH parent AS
(
SELECT id, parentId from tbl WHERE id = @id
UNION ALL
SELECT t.id, t.parentId FROM parent
INNER JOIN tbl t ON t.id = parent.parentid
)
SELECT TOP 1 id FROM parent
order by id asc
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With