SQL query to get parent (job) that does not have specified children (status)

Question

Need help to write sql queries that involve multiple filters on same field of a table.

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 2 tables I have to query for jobs having states FINISHED, CANCELED, FAILED and ACTIVE where

• FINISHED: A Job having status 'Finished'.
• CANCELED: A Job having status '%Canceled' but not ('Finished').
• FAILED: A Job having status '%Failed' but not ('%Canceled' or 'Finished').
• Active: A Job having 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 queries.

Expected output:

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

Thanks in advance.

Answer 1

The version for Oracle is:

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 statuses for each jobid to one string that is called steps. After that I search in string if specific status exists or not. As there can be more than one jobid for such criteria I use listagg again to change result to string. In case you will have 2 finished jobs (with id 1 and 5) , you will see FINISHED: 1 5

The version for MySql with sample SQL Fiddle. It's a little bit longer as we don't 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