Professional Web Applications Themes

Get count of child records - IBM DB2

Hi, I guess this should be pretty simple but can't figure out the right sql query. I have two tables; one is the parent table and the other is a child table. At any time, there could be 0 to N child records associated to one parent record. I am trying to come up with a single sql query that gives the count of child records per parent record. The results generated by the query should look like this: PARENT_RECORD_ID NUMBER_OF_CHILD_RECORDS_FOR_THIS_PARENT_RECORD P1 23 P2 24 P3 4 P4 0 ,where PARENT_RECORD_ID is the unique identifier for a record in the ...

  1. #1

    Default Get count of child records

    Hi,

    I guess this should be pretty simple but can't figure out the right
    sql query. I have two tables; one is the parent table and the other is
    a child table. At any time, there could be 0 to N child records
    associated to one parent record. I am trying to come up with a single
    sql query that gives the count of child records per parent record. The
    results generated by the query should look like this:

    PARENT_RECORD_ID NUMBER_OF_CHILD_RECORDS_FOR_THIS_PARENT_RECORD
    P1 23
    P2 24
    P3 4
    P4 0

    ,where PARENT_RECORD_ID is the unique identifier for a record in the
    parent table and NUMBER_OF_CHILD_RECORDS_FOR_THIS_PARENT_RECORD is the
    total number of child records that have a foreing key that points to a
    particular record in the parent table.

    Thanks in advance for any help.
    Ricardo Guest

  2. #2

    Default Re: Get count of child records

    "Ricardo" <com> wrote in message
    news:google.com... 

    There are several ways to do this, but here is one using a union:

    Select foreign_key, count(*) from child_table
    group by foreign_key

    union all

    select primary_key, 0 from parent_table A
    where not exists (select B.foregin_key from child_table B
    where A.parent_key = B.foreign_key)

    order by 1


    Mark Guest

  3. #3

    Default Re: Get count of child records

    Ricardo <com> wrote:
     

    SELECT p.record_id, COUNT(*)
    FROM parent_table AS p LEFT OUTER JOIN
    child_table AS c ON
    ( p.record_id = c.parent_record_id )
    GROUP BY p.record_id

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

Similar Threads

  1. SQL: Deleting child records recursively
    By darrel in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 4th, 12:05 AM
  2. adding multiple child records
    By acidrain9 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: February 19th, 08:14 PM
  3. ASP Group Same Records and Give Total Count
    By gotcha in forum ASP Database
    Replies: 1
    Last Post: August 6th, 08:50 PM
  4. newbie : trying to count records in mysql table
    By jim in forum PHP Development
    Replies: 1
    Last Post: July 11th, 02:25 PM
  5. Subform listbox records don't reflect master/child link
    By Emilia Maxim in forum Microsoft Access
    Replies: 4
    Last Post: June 30th, 04:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139