IF OBJECT_ID(N'Personnel', 'U') IS NOT NULL
   DROP TABLE Personnel;

GO

CREATE TABLE Personnel
(
   child_id INT NOT NULL PRIMARY KEY,
   parent_id INT REFERENCES Personnel(child_id)
);

INSERT INTO Personnel(child_id, parent_id)
   SELECT 1, NULL UNION ALL
   SELECT 2, NULL UNION ALL
   SELECT 11, 1 UNION ALL
   SELECT 12, 1 UNION ALL
   SELECT 13, 1 UNION ALL
   SELECT 21, 2 UNION ALL
   SELECT 22, 2 UNION ALL
   SELECT 111, 11 UNION ALL
   SELECT 112, 11 UNION ALL
   SELECT 121, 12 UNION ALL
   SELECT 211, 21 UNION ALL
   SELECT 221, 22;

GO

IF OBJECT_ID(N'ufn_GetChilds', 'IF') IS NOT NULL
   DROP FUNCTION ufn_GetChilds;

GO

CREATE FUNCTION ufn_GetChilds(@parent_id INT)
RETURNS TABLE AS
RETURN
(
   WITH ChildsCTE(child_id, parent_id) AS
   (
      SELECT child_id, parent_id
        FROM Personnel
       WHERE parent_id = @parent_id

       UNION ALL

      SELECT Curr.child_id, Curr.parent_id
        FROM ChildsCTE AS Prev
             INNER JOIN
             Personnel AS Curr
             ON Curr.parent_id = Prev.child_id
   )
   SELECT C.child_id, C.parent_id
     FROM ChildsCTE AS C
);

GO

SELECT P.child_id, C.child_count
  FROM Personnel AS P
       CROSS APPLY
       (SELECT COUNT(child_id) AS child_count
          FROM ufn_GetChilds(P.child_id) AS C) AS C;

quote:

---

Originally posted by jcelko

What you are doing is called the Adjacency List. This poor design choice will require that you write slow, complex recursive code like malpashaa showed you.

Look up the Nested Sets model for hierarchies. Buy a copy of TREES & HIERARCHIES IN SQL. Good DDL leads to easy DML.

--CELKO--
Joe Celko, SQL Guru

---