SQL query to get a parent (task) that has no specified children (status)

Question

Need help writing sql queries that include multiple filters in a single table field.

I have 2 tables as shown below.

Job table:

 ID JobId Name StartTime FinishTime 01 001 A 2105:12:10 14:00:00 2105:12:10 14:00:10 02 002 A 2105:12:10 14:00:00 2105:12:10 14:00:00 03 003 A 2105:12:10 14:00:00 2105:12:10 14:00:00 04 004 A 2105:12:10 14:00:00 2105:12:10 14:00:00 

and

Status table:

 ID Status Timestamp JobId 01 Started 2105:12:10 14:00:00 001 02 Step_1_Started 2105:12:10 14:00:00 001 03 Step_1_Finished 2105:12:10 14:00:05 001 04 Step_2_Started 2105:12:10 14:00:05 001 05 Step_2_Finished 2105:12:10 14:00:10 001 06 Finished 2105:12:10 14:00:10 001 ........................................................ 07 Started 2105:12:10 14:00:00 002 08 Step_1_Started 2105:12:10 14:00:00 002 09 Step_1_Failed 2105:12:10 14:00:02 002 ........................................................ 10 Started 2105:12:10 14:00:00 003 11 Step_1_Started 2105:12:10 14:00:00 003 12 Step_1_Failed 2105:12:10 14:00:02 003 13 Step_1_Canceled 2105:12:10 14:00:04 003 ........................................................ 14 Started 2105:12:10 14:00:00 004 15 Step_1_Started 2105:12:10 14:00:00 004 

From these two tables, I need to request jobs in which there are states COMPLETED, CANCELED, FAULT AND ACTIVE, where

• COMPLETED: The task having the status "Finished".
• CANCEL: A job that has the status "% Canceled" but not ("Finished").
• FAILED: A job that has the status "% Failed" but not ("% canceled" or "completed").
• Active: a job that has the status "% Started" but not ("% Failed" or "% Canceled" or "Finished").

I have the following SQL query for Finished that works fine

 SELECT j.jobid FROM Job j JOIN status js ON j.jobid = js.jobid WHERE j.startTime >= '2015:12:10' AND j.startTtime < '2015:12:20' AND js.status = 'Finished'; 

Need help for other inquiries.

Expected Result:

 FINISHED: 001 CANCELED: 003 FAILED: 002 Active: 004 

Thanks in advance.

Answer 1

Version for Oracle:

 with jobList (jobid, steps) as ( select jobid, listagg(Status, ' ') WITHIN GROUP (ORDER BY id) from job_status group by jobid ) select 'FINISHED:' as Status , listagg(jobid, ' ') WITHIN GROUP (ORDER BY jobid) from jobList where instr(steps, 'Finished') > 0 union all select 'CANCELED:' as Status , listagg(jobid, ' ') WITHIN GROUP (ORDER BY jobid) from jobList where instr(steps, 'Finished') = 0 and instr(steps, 'Canceled') > 0 union all select 'FAILED:' as Status , listagg(jobid, ' ') WITHIN GROUP (ORDER BY jobid) from jobList where instr(steps, 'Failed') > 0 and instr(steps, 'Canceled') = 0 and instr(steps, 'Finished') = 0 union all select 'Active:' as Status , listagg(jobid, ' ') WITHIN GROUP (ORDER BY jobid) from jobList where instr(steps, 'Started') > 0 and instr(steps, 'Failed') = 0 and instr(steps, 'Canceled') = 0 and instr(steps, 'Finished') = 0 

Basically, I put all the statuses for each jobid on a single line called steps . After that, I look for a string if a certain status exists or not. Since there can be more than one jobid for such criteria, I use listagg again to change the result to a string. If you have 2 finished tasks (with identifiers 1 and 5), you will see FINISHED: 1 5

Version for MySql with sample SQL Fiddle . This is a little longer since we do not have WITH on MySql.

 select 'FINISHED:' as Status , group_concat( a.jobid separator ' ') as jobList from ( select jobid, group_concat(Status separator ' ') steps from job_status group by jobid ) a where instr(steps, 'Finished') > 0 union all select 'CANCELED:' as Status , group_concat( a.jobid separator ' ') as jobList from ( select jobid, group_concat(Status separator ' ') steps from job_status group by jobid ) a where instr(steps, 'Finished') = 0 and instr(steps, 'Canceled') > 0 union all select 'FAILED:' as Status , group_concat( a.jobid separator ' ') as jobList from ( select jobid, group_concat(Status separator ' ') steps from job_status group by jobid ) a where instr(steps, 'Failed') > 0 and instr(steps, 'Canceled') = 0 and instr(steps, 'Finished') = 0 union all select 'Active:' as Status , group_concat( a.jobid separator ' ') as jobList from ( select jobid, group_concat(Status separator ' ') steps from job_status group by jobid ) a where instr(steps, 'Started') > 0 and instr(steps, 'Failed') = 0 and instr(steps, 'Canceled') = 0 and instr(steps, 'Finished') = 0