Query - For Each Parent Count Children***Resolved***
Hello,
I have a table (Division) which holds division data as well as sub-division data. I have not normalized this table
because not all divisions have sub-divisions. My demo table has the following fields
Div_ID = Primary Key
Parent_ID = a number field, holds the Parent Div_ID if it's a sub-division, else it's the parent = 0
Div_Name = Division or sub-division name
If you think this is an inappropriate way of handling this I'm open to suggestions
The table Division holds the following example data
Code:
Div_ID Parent_ID Div_Name
1 0 Div A
2 1 A SubDiv 1
3 1 A SubDiv 2
4 1 A SubDiv 3
5 0 Div B
6 3 B SubDiv 1
7 0 Div C
I want to return a recordset with the following data
Code:
Div_ID Div_Name ChildCount
1 Div A 3
5 Div B 1
7 Div C 0
I have a basic query, but don't know how to dynamically get the Div_ID for each row.
SELECT DivID, DivName, (SELECT Count(Parent_ID) FROM Division WHERE Parent_ID=[curr Div_ID]) as ChildCount
FROM Division
WHERE Parent_ID=0
Any help, pointers and or tips will be greatly appreciated.
Cheers :D
Al