1 Reply Latest reply: Sep 19, 2013 12:52 PM by user10089645 RSS

    parent - child table issue wrt to count - SQL question

    user10089645


      I have a scenario:

      There are 2 tables (parent and child). lets say, case summary table and task level dimension table.

      for every case id in case summary table, there would be multiple tasks in task level dim table with a flag indicator set to 1 for all tasks.

      but while counting the number of cases active with flag indicator 1 (ofcourse when joining case summary table with task dimension table), for a case id only 1 instance of task needs to be accounted (even though it has more than one task , for counting active cases, the flag ind corresponding to a task in a case if set to 1 , then the case is considered active)..but while joining and taking count of case ids with flag indicator as 1, you get the count of every task row of a case which is incorrect logically. how to discard the rest of child records of a case in child table (task dimension table)?

       

      I am not sure how to achieve this in sql query

       

      Kindly help!

       

      Case summary table

      case id, busininess_unit, agent_name

      1001, admin, Ram

      1002, Finance, Sam

       

      task table

      case id, task_id,task_name, flag_indicator

      1001, 1, 'New', 1

      1001,2, 'Open',1

      1001,3,'In progress',1

      1002, 4, 'New', 1

       

      (In fact task_id is not a big deal... even you can assume task id doesn't exist..only task name ... )

       

      now my question... if my query should get the current active cases (ind=1); as per above it should essentially give 2... but my query gives me 4..you know the reason why.. but how do i get the correct count?

       

      Thanks!