Max from joined table based on value from first table

Question

I have 2 tables.

First holds job details, second one the history of those job runs. First one also contains job period, per customer which is minimum time to wait before running next job for same customer. The time comparison needs to happen on started_on field of second table.

I need to find out the job ids to run next.

Schemas

1. job_details table

CREATE TABLE `job_details` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` varchar(128) NOT NULL,
  `period_in_minutes` int(11) unsigned NOT NULL,
  `status` enum('ACTIVE','INACTIVE','DELETED') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. job_run_history table

CREATE TABLE `job_run_history` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `job_id` int(10) unsigned NOT NULL,
  `started_on` timestamp NULL DEFAULT NULL,
  `status` enum('STREAMING','STREAMED','UPLOADING','UPLOADED','NO_RECORDS','FAILED') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_job_id` (`job_id`),
  CONSTRAINT `fk_job_id` FOREIGN KEY (`job_id`) REFERENCES `job_details` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Sample data for job_details table:

INSERT INTO `job_details` (`id`, `customer_id`, `period_in_minutes`, `status`)
VALUES
    (1, 'cust1', 1, 'ACTIVE'),
    (2, 'cust2', 1, 'ACTIVE'),
    (3, 'cust3', 2, 'ACTIVE');

Sample data for job_run_history table:

INSERT INTO `job_run_history`(`job_id`, `started_on`, `status`)
VALUES
    (1, '2021-07-01 14:38:00', 'UPLOADED'),
    (2, '2021-07-01 14:37:55', 'UPLOADED');

Expected output (When run at 2021-07-01 14:38:56):

id
2,3

• 1. id => 1 did NOT get selected because the last job started within last 1 minute
• 2. id => 2 DID get selected because the last job started more than last 1 minute ago
• 3. id => 3 DID get selected because it has no run history

I have tried this, but this doesn't compare with max of start_time, hence, doesn't work:

select jd.id, max(jrh.started_on) from job_details jd 
left join job_run_history jrh on jrh.job_id=jd.id 
where 
    jd.status='ACTIVE' 
    and (jrh.status is null or jrh.status not in ('STREAMING','STREAMED','UPLOADING')) 
    and (jrh.`started_on` is null or jrh.`started_on` < date_sub(now(), interval jd.`period_in_minutes`*60 second))
group by jd.id;

MySql Version: 5.7.34

Any help please? Thanks in advance..

Answer 1

select jd.id from job_details jd
left join job_run_history jrh on jd.id= jrh.job_id
where jd.status = 'ACTIVE'
group by jd.id
having 
    max(jrh.started_on) < current_timestamp - interval max(jd.period_in_minutes) minute
    or 
    max(jrh.id) is null

I'm not sure what's this filter about since you didn't explain it in your question so I didn't put it in the query: jrh.status not in ('STREAMING','STREAMED','UPLOADING'). However, I'm sure you can implement it in the query I posted.