jlyh jlyh - 3 months ago 7
MySQL Question

MySQL Query to fetch Distinct Rows with latest status

I have 3 tables, namely - areas, works and jobs.

areas works jobs
----- ----- -----
area_id work_id area_id (FK)
area_name task work_id (FK)
area_type app_area status
updated_at


I'm trying to select the total list of areas cross joined with works such that I have all the permutations for areas vs works, then have the LATEST status of that combination, if it exists. I want distinct rows for each area_id-work_id combination.

I put together the below query statement but some rows have statuses displayed as NULL when they actually exist. My guess is there's something wrong with my inner SELECT statement but try as I may, I could not get it to work, any idea what's wrong with my statement?

SELECT area_name, works.task, jobs.status
FROM areas
CROSS JOIN works ON works.work_id = works.work_id
LEFT JOIN jobs ON jobs.status = (SELECT jobs.status FROM jobs ORDER BY jobs.updated_at DESC LIMIT 1) AND
(jobs.work_id = works.work_id AND jobs.area_id = areas.area_id)
WHERE works.app_area = 'zone' AND areas.area_type = 'zone'
ORDER BY areas.area_id, works.work_id, jobs.updated_at;

Answer

Your logic for the last status should be using the date not the status. The logic looks like this:

SELECT a.area_name, w.task, j.status
FROM areas a CROSS JOIN
     works w LEFT JOIN
     jobs j
     ON j.work_id = w.work_id AND j.area_id = a.area_id AND
        j.updated_at = (SELECT MAX(j2.updated_at)
                        FROM jobs j2
                        WHERE j2.work_id = w.work_id AND j2.area_id = a.area_id
                       )
WHERE w.app_area = 'zone' AND a.area_type = 'zone'
ORDER BY a.area_id, w.work_id, j.updated_at;

This also fixes some other problems, such as having an ON clause with CROSS JOIN.