MYSQL - not getting correct data MAX

Question

I have a list of cases containing agencies. All agencies contain tasks which have a status assigned to it. 0 = complete, 1 = ongoing and 2 = overdue.

I am trying to show for all cases if there is any tasks overdue. At the moment my query shows the status as 1, even though there are some tasks overdue.

SQL FIDDLE HERE http://sqlfiddle.com/#!2/a4394/3

I don't know what I need to change in order to pull out the correct data. Any ideas?

Any help would be greatly appreciated!!

Answer 1

Ignoring that you're writing a MySQL-only SELECT, your joins are wrong; you're joining cc.case_ID on caa.ID when you actually want caa.case_ID:

SELECT cc . * , a.ID, MAX(cta.status) AS current_status
FROM cases_complete cc, agencies a, cases_agency_association caa,
  cases_task_association cta
WHERE cc.case_ID = caa.case_ID
AND caa.agency_ID = a.ID
AND cta.agency_association_ID = caa.ID
GROUP BY cc.case_ID

However @meewoK's approach, and the comment about not abusing GROUP BY are both worth thinking through from the point of view of coming up with something clearer, more maintainable, and hopefully more portable / future compatible as well.