# Get Hierarchial Data in Self-Referencing Table (Parents , Childs) [closed]

In the following Fiddle,

I will pass a sno and I need all the parents of that sno in a table. And all the children of that sno in another table

Please refer to this question. Since both are related, they use the same table.

I need to display top 10 levels in a table.

-

## closed as not a real question by Max Vernon, Aaron Bertrand♦, Mark Storey-Smith, ypercubeᵀᴹ, MarianApr 6 '13 at 21:36

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

For immediate parent and its ancestor

``````WITH Parent (refid, Sno, Level)
AS
(
SELECT refid, Sno, 0 AS Level
FROM dbo.MyTable m
WHERE Sno=4
UNION ALL
SELECT m.refid, m.Sno, Level+1
FROM dbo.MyTable m
INNER JOIN Parent p
ON p.refid = m.Sno
)
SELECT refid, Level
FROM Parent;
``````

For immediate child and all the grandchild

``````WITH Child (refid, Sno, Level)
AS
(
SELECT refid, Sno, 0 AS Level
FROM dbo.MyTable m
WHERE Sno=4
UNION ALL
SELECT m.refid, m.Sno, Level+1
FROM dbo.MyTable m
INNER JOIN Child p
ON p.Sno = m.refid
)
SELECT Sno, Level
FROM Child
WHERE Sno <> 4;
``````

The level stated above is the hierarchy of the parent/child.

-

If you're looking for a way to see who the next related node is, that's really simple, just:

``````-- check for the parents of a particular Sno
select Refid
from dbo.Mytable
where Sno = 'X'

--check for the children of a particular Sno
select Sno
from dbo.MyTable
where RefId = 'X'
``````

If you however want to get the whole tree, you need to use a recursive technique

``````--First, find all the children and their children etc of a particular Sno:
WITH RECUR AS (
SELECT Sno, REFID
FROM dbo.MyTable
WHERE SNO = '1'
UNION ALL
SELECT a.SNO, a.REFID
FROM dbo.MyTable A
INNER JOIN RECUR B ON B.SNO = A.REFID
)
SELECT Refid as Parent, Sno as Child
FROM RECUR

-- Find all the parents and ancestors of a particular Sno:
WITH RECUR AS (
SELECT Sno, REFID
FROM dbo.MyTable
WHERE SNO = '1'
UNION ALL
SELECT a.SNO, a.REFID
FROM dbo.MyTable A
INNER JOIN RECUR B ON B.REFID = A.SNO)
SELECT Sno as Child, Refid as Parent
FROM RECUR
``````
-