MySQL Join and Subqueries

Question

I currently have the following tables:

Case_Workflows

case_id (PK)  |  work_id (PK)  |    date
     1        |     1          |  2011-12-12
     1        |     4          |  2011-12-13
     2        |     6          |  2011-12-18

Workflows

  work_id (PK)  |  status_id
     1          |     1
     2          |     1
     3          |     1
     4          |     2
     5          |     2
     6          |     3

Statuses

  status_id (PK)  |   title
     1            |     abc
     2            |     def
     3            |     ghi

What I am attempting to do is pull a count of the total number of cases with a specific status such as 'abc'. The snag is that each case can have multiple workflows and I only want the single most recent one for each case.

The end result should be:

Status: abc - Count: 2

This is what I have so far:

SELECT COUNT(cases.case_id) as countNum  
FROM $this->_caseTable  
JOIN case_workflows 
  ON cases.case_id = cases_workflows.case_id  
JOIN workflows 
  ON cases_workflows.workflow_id = workflows.workflow_id  
JOIN statuses 
  ON workflow.status_id = statuses.status_id
WHERE cases.date > '2011-12-12'
AND cases.date <= '2011-12-18' 

What I am unsure on is how to first select the latest work_id for each case, and then grabbing its status_id to match it to a WHERE clause such as WHERE statuses.title = 'abc'

Answer 1

SELECT COUNT(*) as countNum  
FROM $this->_caseTable  
JOIN workflows 
  ON workflows.workflow_id =  
     ( SELECT workflow_id 
       FROM cases_workflows AS mcwf
       WHERE mcwf.case_id = cases.case_id
       ORDER BY date DESC 
       LIMIT 1
     )
JOIN statuses 
  ON workflow.status_id = statuses.status_id
WHERE cases.date > '2011-12-12'
  AND cases.date <= '2011-12-18'
  AND statuses.title = 'abc'

Answer 2

From what I'm understanding here, you need to add statuses.title to your SELECT clause, and then add a GROUP BY clause:

SELECT statuses.title, COUNT(cases.case_id) as countNum  
FROM $this->_caseTable  
JOIN (SELECT case_id, work_id, max(date) 
      FROM case_workflows 
      GROUP BY work_id 
      WHERE case_id = cases.case_id) cw 
  ON cases.case_id = cw.case_id  
JOIN workflows 
  ON cw.workflow_id = workflows.workflow_id  
JOIN statuses 
  ON workflow.status_id = statuses.status_id
GROUP BY statuses.title
WHERE cases.date > '2011-12-12'
AND cases.date <= '2011-12-18'