Recursive Cte to Find Parent Records

RECURSIVE QUERY - PARENT/CHILD

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;

CTE Recursive Query to Grandparents

You need to add the same isdeleted = 0 AND isinedit = 0 predicate to the INNER JOIN childParent CP source.

...but if you do that you make your CTE query very fiddly and if you have to repeat the same thing over-and-over there's probably a better way to do it.

...and there is! A SELECT query can have multiple CTE expressions:

;
WITH filtered AS
(
    SELECT
        a.id,
        a.name,
        a.parent_id,
    FROM
        dbo.Table
    WHERE
        IsDeleted = 0
        AND
        IsInEdit = 0 
)

WITH cte AS
(
    SELECT
        a.id,
        a.name,
        a.parent_id
    FROM
        filtered

    UNION ALL

    SELECT
        a.id,
        a.name,
        a.parent_id
    FROM
        filtered
        INNER JOIN cte ON a.parent_id = cte.id
)
SELECT  
    *
FROM
    cte
ORDER BY
    id

CTE to get all children and nested children of every parent

In first part of cte select all the rows with ownid as root id. Then in second part (after union all) select parentid as rootid.

Schema and insert statements:

 create table users (UserID int,  ParentID int);
 insert into users values (1,        NULL);
 insert into users values (2,        1);
 insert into users values (3,        1);
 insert into users values (4,        2);
 insert into users values (5,        2);
 insert into users values (6,        5);
 insert into users values (7,        6);
 insert into users values (8,        6);
 insert into users values (9,        NULL);

Query:

 with cte as
 (
   select userid rootid, userid, parentid from users 
   union all
   select cte.rootid rootid, users.userid, users.parentid from users
   inner join cte on users.parentid=cte.userid
 )
 select rootid parentid,userid from cte
 order by rootid ,userid
 option (maxrecursion 0)

Output:

WITH RCTE AS
  (
    SELECT @childID as parentId, NULL as childid, 1 AS Lvl

    UNION ALL

    SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
    )
   SELECT TOP 1 id, Name
   FROM RCTE r
   inner JOIN dbo.Person p ON p.id = r.ParentId
   ORDER BY lvl DESC;

; with  parents as
        ( 
        select  child_product_id
        ,       parent_product_id
        from    product
        where   child_product_id = 392193
                and parent_product_id = 392193
        union all 
        select  e.child_product_id
        ,       e.parent_product_id
        from    parents m
        join    product e
        on      e.parent_product_id = m.child_product_id
        )
select  *
from    parents  
option  (maxrecursion 0)

 ;with leafs as (
select *,ins.Coef*ins.price [total] from Instances ins where price is not null
union all
select ins.*,leafs.total*ins.Coef [total] from leafs
    inner join Instances ins on ins.ID=leafs.ParentID

)
select ID,sum(total)  Totalprice  from leafs 
group by ID
order by ID

ID  Totalprice
1   187.5
2   12.5
3   50
4   10
5   15
6   12
7   13