Query - For Each Parent Count Children
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Query - For Each Parent Count Children

  1. #1
    Join Date
    May 2003
    Posts
    49

    Question 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.

  2. #2
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    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

  3. #3
    Join Date
    May 2003
    Posts
    49
    Rigatoni,

    That's brilliant!!

    Many, many thanks! I'm indebted

    Cheers Al

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    May 2003
    Posts
    49
    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
  •  


Click Here to Expand Forum to Full Width