I'm struggling with retrieving the correct data from a MySQL 8 db having two relevant tables. I want to get a list of all open jobs and jobs that closed today that have a specific

process_id

. First a

jobs

table which has a pk

job_id

(INT) and an associated

process_id

(INT). Then a

jobs_log

table that logs changes to a particular job's status and so has relevant

job_id

,

logdate

(timestamp) and

job_status

(INT) fields. I want to query the db to get a recordset of all current `job_id`s with a specific

process_id

. In this case, "current" means the most recent

job_status

entry as long as: • that entry's

job_status

is < 2 OR • that entry's

job_status

is >= 2 but its most recent

logdate

is equal to today's date

CURDATE()

(i.e. it was changed to 2+ sometime today) The result should have the

job_id

, its current

job_status

and the

logdate

of that status change.

Could you try this:

SELECT log.job_id, log.logdate, log.job_status, jobs.process_id
FROM 
	jobs_log log
	INNER JOIN (
		SELECT job_id, max(logdate) logdate
		FROM jobs_log
		GROUP BY job_id
	) current_jobs on log.job_id = current_jobs.job_id AND log.logdate = current_jobs.logdate
	INNER JOIN jobs ON log.job_id = jobs.job_id
WHERE
	log.job_status < 2 OR (log.job_status >=2  AND DATE(log.logdate) = CURDATE() )

The first two tables join would get current jobs status, then you could join the jobs table and use where to apply the filters.