For a Parent-Child self-referencing table, there are going to be different routines to be done:
Build the hierarchical structure
You already have the database table, you are going to need a recursive function to generate a dataset that reflects how that structure looks as a tree. There are many examples of sample code for this available, including this one with some management functions located here
SQL Queries to Manage Hierarchical or Parent-child Relational Rows in SQL Server[
^]
Count the children for any particular parent
This is going to be a simple one; basically you need to do a count of children for each parent
Tips & Hints
When my web applications using parent-child structures are edited; I fire off a stored procedure that rebuilds the hierarchy and saves it into a new table so that the recursion does not need to be done at every call.
On the parent-cild table itself, there are fields that contain the counts for children as well as how many items are in that category.
Update
Sample Code for Counting the Children
Please note the square brackets around [Name]
; this is because it is a reserved word. It is generally recommended not to use these when you are designing tables etc as it can cause problems if the square brackets are omitted
SELECT p.[Name], Children = Count(c.ParentID)
FROM DocuTree p
LEFT JOIN DocuTree c ON p.ChildID = c.PhildID
GROUP BY p.[Name]