MySql select from nested query with max date constraint

Example of MySql select from nested query with max (or min) constraint.

 
SELECT company_id FROM (
	SELECT co.company_id, DATE(tsk.modified) mm
        FROM company co
        LEFT JOIN project p ON p.company_id = co.company_id
        LEFT JOIN task tsk ON tsk.project_id = p.project_id
        WHERE 1
            AND co.active < 1
            AND co.pay_status < 0
            AND co.test_account < 1
) AS tmpt
GROUP BY company_id
HAVING MAX(mm) < DATE(DATE_SUB(NOW(),INTERVAL 1 YEAR))
ORDER BY `tmpt`.`company_id`  DESC

Leave a Reply