RECURSIVE QUERY – PARENT/CHILD

Say I have a table like so:

     id      Parentid    childid

    --------------------------------
      01         null       02
      02         01         03
      03         02         04
      04         03         06
      05         051        101
      06         04         055

Let’s say I’m always given a middle value so one that has both children and parent nodes which at this time I don’t know what they are. e.g I am given ID: 4. I have written a recursive query which gets me both the Parents and the child records but I’m quite unsure if it the CORRECT method of going about it but also if its the quickest.

with x as (
  --These are the anchor (the parents)
  select *
  from table with (nolock)
  where id= 04
  ),
  parents as (
  SELECT * 
    FROM x
    UNION ALL
    SELECT p.* 
    FROM parents JOIN
         table p
         ON parents.ID = p.childid

  ),
  children as (
  SELECT top 1 * 
    FROM x
    UNION ALL
    SELECT p.* 
    FROM children JOIN
         table p
         parents.id = p.parentid

  )

SELECT distinct * 
FROM parents
UNION
SELECT distinct *
FROM children;

This is working with how i want the results to come back so will return id: 01,02,03,04,06 as they are either a parent or child of the ID 04 etc.

Essentially what I wished to get at was what is the best way of doing a recursive query to get both parent and child of a given record, where to find the children you must use 1 column and to get parents must use another etc.

Answer

This is Pseudo SQL terms, but you only need 2 CTEs, and no DISTINCT to get what you are after. Transversing hierachical data, that doesn’t use the hierachyid datatype is never going to be as effecient as it could be, as you need recursion.

Anyway, you would effectively just want this:

USE Sandbox;
GO

CREATE TABLE dbo.YourTable (ID int, ParentID int);

INSERT INTO dbo.YourTable (ID,
                           ParentID)
VALUES(1,NULL),
      (2,NULL),
      (3,1),
      (4,1),
      (5,2),
      (6,3),
      (7,4),
      (8,4),
      (9,7),
      (10,8);
GO
DECLARE @ID int = 4;

WITH Parents AS(
    SELECT YT.ID,
           YT.ParentID
    FROM dbo.YourTable YT
    WHERE YT.ID = @ID
    UNION ALL
    SELECT YT.ID,
           YT.ParentID
    FROM Parents P
         JOIN dbo.YourTable YT ON P.ParentID = YT.ID),
Children AS(
    SELECT YT.ID,
           YT.ParentID
    FROM dbo.YourTable YT
    WHERE YT.ID = @ID
    UNION ALL
    SELECT YT.ID,
           YT.ParentID
    FROM Children C
         JOIN dbo.YourTable YT ON C.ID = YT.ParentID)
SELECT ID, ParentID
FROM Parents P
UNION ALL
SELECT ID, ParentID
FROM Children C
WHERE C.ID != @ID --Stops the initial row being displayed twice
ORDER BY ID ASC; 

GO

DROP TABLE dbo.YourTable;