I have a training_stats table (current due training) and I also have a completed_training table.
What I want to do is query due training with the last completed date from the completed table.
I've nearly got what I want, I get the due training, but they are duplicated with each completed record(as there are many completed records to each current due), and I only want single rows and the latest completed date.
I've been trying to use MAX, and when I run the MAX query independently, I get the last record. But when the MAX query is in the join, it is returning all completed rows.
This is the query that I am using:
SELECT s.course_stat_id ,o.org_name ,u.id ,u.first_name ,u.last_name ,a.area_id ,a.area_name ,tc.course_id ,tc.course_name ,s.assigned_on ,s.due ,s.pass_mark ,s.completed_on ,completed.complete_training_id ,completed.complete_date FROM training_stats s JOIN organisations o ON o.org_id = s.org_id LEFT JOIN ( SELECT complete_training_id ,user_id ,area_id ,course_id ,max(completed_on) AS complete_date FROM completed_training GROUP BY complete_training_id ) completed ON completed.user_id = s.user_id AND completed.area_id = s.area_id AND completed.course_id = s.course_id LEFT JOIN users u ON u.id = s.user_id LEFT JOIN areas a ON a.area_id = s.area_id LEFT JOIN training_courses tc ON tc.course_id = s.course_id WHERE u.active = 1 AND o.active = 1 AND s.assigned = 1
Can you see what I am doing wrong?