3 Replies Latest reply: Feb 3, 2013 8:25 AM by 382809 RSS

    comparing counts of child table records

    382809
      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
          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
            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
              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!!!