Results 1 to 6 of 6

Thread: Recursive Query

  1. #1
    Join Date
    Jul 2011
    Posts
    9

    Unanswered: Recursive Query

    Hi,

    I'm new to database world. I need help in DB2 query to find out projects which are completed based on their task status.

    Here is data:


    ProjID TaskID Status
    P1 T11 Complete
    P1 T12 Complete
    P1 T13 Complete
    P2 T21 Complete
    P2 T22 Active
    P3 T31 Active
    P4 T41 Complete
    P4 T42 Active
    P4 T43 Complete
    P4 T44 Active
    P5 T51 Complete
    P6 T61 Active
    P6 T62 Active
    P7 T71 Complete
    P7 T72 Complete

    Out put should display list of projects which are completed. Based on it's all task completed.

    Output
    ProjID
    P1
    P5
    P7


    What kind of query can give me such result?

    Thanks for your help,

    RR

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There is nothing recursive about it. You just select project IDs for which there exist no tasks with status other than "Complete".
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2011
    Posts
    9
    But this will give me only list of completed task but how i know that all tasks from that project is completed?

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    select project from table a
    where not exists(select 1 from table b
                           where a.project = b.project
                              and status <> 'COMPLETED')
    this would give you all projects as long as the only task status' are completed, just as N_I suggested.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    SELECT proj_id
     FROM  proj_task_status
     GROUP BY
           proj_id
     HAVING
           COUNT( NULLIF(status , 'Complete') ) = 0
    ;
    "COUNT( NULLIF(status , 'Complete') )" counts number of status which are not 'Complete'.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If status was NULLable,
    please try
    Code:
    SELECT proj_id
     FROM  proj_task_status
     GROUP BY
           proj_id
     HAVING
           COUNT( NULLIF( COALESCE(status , '') , 'Complete' ) ) = 0
    ;

Posting Permissions

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