jointue.sql

SELECT 
	* 
FROM AppliControle AS ac
INNER JOIN applicontrole_controlinterne AS aci ON aci.applicontrole_id = ac.id
INNER JOIN ControlInterne AS ci ON aci.controlinterne_id = ci.id
INNER JOIN Task AS t ON ci.task_id = t.id
INNER JOIN (
		SELECT 
			aci2.applicontrole_id AS id,
			max(t2.date) as recent
		FROM Task as t2
		INNER JOIN ControlInterne AS ci2 ON ci2.task_id = t2.id
		INNER JOIN applicontrole_controlinterne AS aci2 ON aci2.controlinterne_id = ci2.id
		GROUP BY (aci2.applicontrole_id)
	) as tRecent ON tRecent.id = ac.id AND tRecent.recent = t.date;