Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In the following Fiddle,

sqlfiddle

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.

share|improve this question

closed as not a real question by Max Vernon, Aaron Bertrand, Mark Storey-Smith, ypercube, Marian Apr 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.

add comment

2 Answers

up vote 3 down vote accepted

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.

More information on recursive CTE can be found here http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

share|improve this answer
add comment

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
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.