Query - For Each Parent Count Children

[Running Bear]

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

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

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
Al

[rigatoni]

1 SELECT Div_ID,
2 Div_Name,
3 (SELECT Count(Parent_ID)
4 FROM Division d2
5 WHERE Parent_ID=d1.div_id) as ChildCount
6 FROM Division d1
7* WHERE Parent_ID=0
scott@AMSAA>
scott@AMSAA> /

DIV_ID DIV_NAME CHILDCOUNT
---------- ---------- ----------
1 div a 3
5 Div B 1
7 Div C 0

[Running Bear]

Rigatoni,

That's brilliant!!

Many, many thanks! I'm indebted

Cheers Al

[jmodic]

You can also use self outer join:

SELECT t1.div_id,
       t1.div_name,
       COUNT(t2.div_id) AS childcount
  FROM divsion t1,
       division t2
 WHERE t1.parent_id = 0
   AND t1.div_id = t2.parent_id(+)
 GROUP BY t1.div_id, t1.div_name

[Running Bear]

JModic,

Thanks a lot for your reply, didn't think of doing it that way.

Cheers Al