-1

I have the following SQL Server 2008 table and data:

CREATE TABLE dbo.MyTable (Sno int, refid int);

INSERT INTO dbo.MyTable VALUES
  (17, 8),
  (16, 8),
  (15, 7),
  (14, 7),
  (13, 6),
  (12, 6),
  (11, 5),
  (10, 5),
  (9, 4),
  (8, 4),
  (7, 3),
  (6, 3),
  (5, 2),
  (4, 2),
  (3, 1),
  (2, 1),  
  (1, 0);

Note that Sno 2 and 3 are referencing Sno 1 in the same table (self-reference). So 2 and 3 are children of 1 and 1 is the parent of 2 and 3.

Requirement

I will pass in a single Sno value (e.g. 4) and I need:

  1. All the parents of that Sno in a result set.
  2. All the children of that Sno in another result set.

I need to display the top 10 levels for each result set.

This illustrates the relationships and shows what I have tried so far:

SELECT refid FROM dbo.MyTable 
WHERE Sno = (SELECT refid FROM dbo.MyTable WHERE Sno = 4);
GO
SELECT refid FROM dbo.MyTable WHERE Sno = 4;
GO
Select Sno FROM dbo.MyTable 
WHERE refid = (SELECT refid FROM dbo.MyTable WHERE Sno = 4);
GO

SQL Fiddle

4

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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