Find the number of descendant nodes in a SQL query

Question

0.00/5 (No votes)

See more:

I am currently creating a Organizational Chart and I need a total count of all the nodes under a certain employee.

I researched that I need to use recursive SQL. But it seems to not be working in my query. Instead I repeatedly (I know this seems wrong) put the query in a temp table and add the Children of the employee and so on. There is a sample data:

SQL

INSERT INTO @T (ID, name, AdvisorID) VALUES
(1,  'Euler', NULL),
(2,  'Lagrange', 1),
(3,  'Laplace', 1),
(4,  'Fourier', 2),
(5,  'Poisson', 2),
(6,  'Dirichlet', 4),
(7,  'Lipschitz', 6),
(8,  'Klein', 6),
(9,  'Lindemann', 8),
(10, 'Furtwangler', 8),
(11, 'Hilbert', 9),
(12, 'Taussky-Todd', 10);

SQL

SELECT ID
, name
, AdvisorID
, (SELECT COUNT(*) FROM @T WHERE AdvisorID = e.ID) AS ChildrenCount
INTO #Temp1
FROM @T e

SELECT *
, (SELECT ISNULL((SUM(ChildrenCount)+e.ChildrenCount),0) FROM #Temp1 WHERE AdvisorID = e.ID) AS Total
INTO #Temp2
FROM #Temp1 e

SELECT * 
, (SELECT ISNULL((SUM(Total)+e.ChildrenCount),0) FROM #Temp2 WHERE AdvisorID = e.ID AS Total2
INTO #finalTemp
FROM #Temp2 e

and so on...

Thanks for the help. I just want to optimize my repetitive code because it will take too long to load in the page.

What I have tried:

How to find out total number of immediate children of a parent in HierarchyID – SQLServerCentral[^]
postgresql - Recursive SQL - count number of descendants in hierarchical structure - Stack Overflow[^]

Posted 25-Apr-19 0:03am

Michelle Anne Rigor

Updated 25-Apr-19 0:47am

Add a Solution

Comments

[no name] 25-Apr-19 6:28am

Have a look here: sql server - Counting number of children in hierarchical SQL data - Stack Overflow[^]

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

User 11060979 · Accepted Answer · 2019-04-25T00:47:00

In case you can use CTE, then based on this: sql server - Counting number of children in hierarchical SQL data - Stack Overflow[^]

The SQL lokks like

;WITH ChildrenCTE AS (
  SELECT  RootID = ID, ID
  FROM    Tbl1
  UNION ALL
  SELECT  cte.RootID, d.ID
  FROM    ChildrenCTE cte
          INNER JOIN Tbl1 d ON d.AdvisorID = cte.ID
)
SELECT  d.ID, d.AdvisorID, d.Name, cnt.Children
FROM    Tbl1 d
        INNER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.ID

and the result I get is

ID  AdvisorID	Name		    Children
--  ---------	-------------	--------
1	NULL	Euler              11
2	1		Lagrange           9
3	1		Laplace            0
4	2		Fourier            7
5	2		Poisson            0
6	4		Dirichlet          6
7	6		Lipschitz          0
8	6		Klein              4
9	8		Lindemann          1
10	8		Furtwangler        1
11	9		Hilbert            0
12	10		Taussky-Todd       0

Find the number of descendant nodes in a SQL query

1 solution

Solution 1

Add your solution here

Preview 0

Find the number of descendant nodes in a SQL query

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us