mysql query reminder for igsr-analysis project

mysql query reminder for igsr-analysis project

#split input_id from job
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(input_id, ' ', 1), ' ', -1) as prefix,
       SUBSTRING_INDEX(SUBSTRING_INDEX(`input_id`, ' ', 2), ' ', -1) as data_id FROM job;

#select analysis_data_id using a join
select * from analysis_data where analysis_data_id in (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`input_id`, ' ', 2), ' ', -1) as data_id FROM job where analysis_id=30 and status='RUN')

#select analysis_data and filtering according to one chromosome (chr8 in this case):
select * from analysis_data where analysis_data_id in (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`input_id`, ' ', 2), ' ', -1) as data_id FROM job where analysis_id=29 and status='RUN') and data like '%8:%';

#get worker information on FAILED jobs
select * from worker where worker_id in (select worker_id from role where role_id in (select role_id from job where analysis_id=30 and status='FAILED'))

#get cause of death counts for FAILED jobs:
select cause_of_death,COUNT(*) from worker where worker_id in (select worker_id from role where role_id in (select role_id from job where status='FAILED')) GROUP BY cause_of_death
MEMLIMIT	532
RUNLIMIT	361

# Getting the start time of jobs that are in 'RUN' in order to contact systems to pass them to long queue
select * from worker where worker_id in (select worker_id from role where role_id in (select role_id from job where (analysis_id=14) and status='RUN') and (when_started>='2019-04-17 00:00:00' ))
//
#getting all jobs that have failed by a certain reason
select job_id from job where role_id in (select role_id from role where worker_id in (select worker_id from worker where cause_of_death='RUNLIMIT')) and status='FAILED'
//
#getting the process ids for a list of jobs
select process_id from worker where worker_id in (select worker_id from role where role_id in (select role_id from job where job_id in (3346,4850)))
//
#getting all jobs completed from a certain date:
select * from job where status='DONE' and when_completed>'2019-07-26 00:00:00' and analysis_id in (14,15,16)
//
#get worker table info on a list of job ids:
select w.*,j.job_id from job j, role r, worker w where (j.role_id=r.role_id and w.worker_id=r.worker_id) 
and j.status='FAILED' and j.job_id in (218225,223113)
//
#get the log message for failed jobs:
select msg from log_message where job_id in (select job_id from job where status='FAILED')