-
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
Al
Last edited by Running Bear; 12-09-2003 at 10:26 AM.
-
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
-
Rigatoni,
That's brilliant!!
Many, many thanks! I'm indebted
Cheers Al
-
You can also use self outer join:
Code:
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
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
JModic,
Thanks a lot for your reply, didn't think of doing it that way.
Cheers Al
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|