Getting All the Children of a Parent Using Mssql Query

DECLARE @Id int = your_UnitId
;WITH cte AS 
 (
  SELECT a.Id, a.parentId, a.name
  FROM customer a
  WHERE Id = @Id
  UNION ALL
  SELECT a.Id, a.parentid, a.Name
  FROM customer a JOIN cte c ON a.parentId = c.id
  )
  SELECT parentId, Id, name
  FROM cte

 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);

 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)

parentid	userid
1	1
1	2
1	3
1	4
1	5
1	6
1	7
1	8
2	2
2	4
2	5
2	6
2	7
2	8
3	3
4	4
5	5
5	6
5	7
5	8
6	6
6	7
6	8
7	7
8	8
9	9

Sql Get all children of a parent

Will return all areas that are a child of city with id of 1 (e.g. New York). You can change that number to any other city to return it's children too

select * from areas where path like '%/1/%'

How to get all children of parent item when all are in the same row and table

You can use a Recursive CTE:

DECLARE @pID VARCHAR(20) = 'A'

;WITH CTE AS (
   SELECT ChildPart
   FROM mytable
   WHERE Part = @pID

   UNION ALL

   SELECT t1.ChildPart
   FROM mytable AS t1
   INNER JOIN CTE AS t2 ON t1.Part = t2.ChildPart
)
SELECT ChildPart
FROM CTE

SQL how to get parent and its all children and children of children... etc

You can use Oracle's hierarchial queries to get this along with the level of tree:

Demo

SELECT CHILD,  LEVEL FROM TABLE1
START WITH PARENT = 'Bob'
connect by prior child = parent;

Select all parents having a child with specific property

You can use exists:

select p.*
from parent p
where exists (select 1
              from child c
              where c.parent_id = p.id and c.property = ?
             );

Get all parents for a child

Try this to get all parents of a child

;with name_tree as 
(
   select id, parentid
   from Users
   where id = 47897 -- this is the starting point you want in your recursion
   union all
   select C.id, C.parentid
   from Users c
   join name_tree p on C.id = P.parentid  -- this is the recursion
   -- Since your parent id is not NULL the recursion will happen continously.
   -- For that we apply the condition C.id<>C.parentid 
    AND C.id<>C.parentid 
) 
-- Here you can insert directly to a temp table without CREATE TABLE synthax
select *
INTO #TEMP
from name_tree
OPTION (MAXRECURSION 0)

SELECT * FROM #TEMP

Click here to view result

EDIT :

If you want to insert into a table variable, you can do something like:

-- Declare table varialbe
Declare @TABLEVAR table (id int ,parentid int)

;with name_tree as 
(
   select id, parentid
   from #Users
   where id = 47897 -- this is the starting point you want in your recursion
   union all
   select C.id, C.parentid
   from #Users c
   join name_tree p on C.id = P.parentid  -- this is the recursion
   -- Since your parent id is not NULL the recursion will happen continously.
   -- For that we apply the condition C.id<>C.parentid 
    AND C.id<>C.parentid 
) 
-- Here you can insert directly to table variable
INSERT INTO @TABLEVAR
select *
from name_tree
OPTION (MAXRECURSION 0)

SELECT * FROM @TABLEVAR

Click here to view result

---

Related Topics