This discussion is archived
3 Replies Latest reply: Feb 3, 2013 6:25 AM by 382809 RSS

comparing counts of child table records

382809 Newbie
Currently Being Moderated
I have the below 3 tables:

Table1 - Parent Table
Table2- A child table that has many records for each record in Table1 (row_id in Table1 maps to par_row_id in Table2)
Table3- A child table that has many records for each record in Table1 (row_id in Table1 maps to par_row_id in Table3)

I would like to run an Oracle SQL query to identify all distinct row_id on Table1, for which the count of child records in Table2 does not match with the count of child records in Table3

I request help with identifying the best way to identify these parent table records. If possible an example too, please.

Thanks

Edited by: cseshan on Feb 3, 2013 1:35 AM
  • 1. Re: comparing counts of child table records
    jeneesh Guru
    Currently Being Moderated
    with child1_cnt as
    (
    select par_row_id,count(*) cnt
    from table2
    group by par_row_id
    ),
    child2_cnt as
    (
    select par_row_id,count(*) cnt
    from table3
    group by par_row_id
    ),
    all_counts as
    (
    select p.row_id,nvl(c1.cnt,0) c1_cnt,nvl(c2.cnt,0) c2_cnt
    from table1 p
     left outer join child1_cnt c1 
       on ( p.row_id = c1.par_row_id )
     left outer join child2_cnt c2
       on ( p.row_id = c2.par_row_id )
    )
    select row_id,c1_cnt,c2_cnt
    from all_counts
    where c1_cnt != c2_cnt;
  • 2. Re: comparing counts of child table records
    jeneesh Guru
    Currently Being Moderated
    Just adding the simplest example below (With sample data which you were supposed to provide)
    create table table1(row_id number primary key);
    
    create table table2(par_row_id number references table1);
    
    create table table3(par_row_id number references table1);
    
    insert into table1 values(1);
    insert into table2 values(1);
    insert into table3 values(1);
    
    insert into table1 values(2);
    insert into table2 values(2);
    insert into table2 values(2);
    
    insert into table1 values(3);
    insert into table3 values(3);
    insert into table3 values(3);
    
    insert into table1 values(4);
    insert into table2 values(4);
    insert into table2 values(4);
    insert into table2 values(4);
    insert into table3 values(4);
    insert into table3 values(4);
    
    commit;
    
    
    with child1_cnt as
    (
      select par_row_id,count(*) cnt
      from table2
      group by par_row_id
    ),
    child2_cnt as
    (
      select par_row_id,count(*) cnt
      from table3
      group by par_row_id
    ),
    all_counts as
    (
      select p.row_id,nvl(c1.cnt,0) c1_cnt,nvl(c2.cnt,0) c2_cnt
      from table1 p
       left outer join child1_cnt c1 
         on ( p.row_id = c1.par_row_id )
       left outer join child2_cnt c2
         on ( p.row_id = c2.par_row_id )
    )
    select row_id,c1_cnt,c2_cnt
    from all_counts
    where c1_cnt != c2_cnt;
    
    
    ROW_ID C1_CNT C2_CNT
    ------ ------ ------
         2      2      0 
         4      3      2 
         3      0      2 
  • 3. Re: comparing counts of child table records
    382809 Newbie
    Currently Being Moderated
    Thanks a million Jeneesh!! This query worked really well for me.

    Really simple and easy. Helped me learn a style of SQL that I have not tried before.

    Sorry that I had missed the sample data, was my first time here. I'll ensure that henceforth.

    Thank You!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points