check if master record date in table A is later than latest records date in joined table B

I have a master table people

people fields: Id,AlterationDate

And开发者_开发技巧 an actions table actions in a one to many linkage

actions fields: Id, PeopleID, CreationDate

I want to find all records in people whose alteration dates are later than the latest creation date in actions joined by the persons id.

MySQL is the dialect, Dates are DateTime fields.

---

select people.* from people  
join actions on actions.peopleID = people.id  
group by people.id  
having max(action.CreationDate) < people.AlterationDate

---

try

select p.peopleid 
from actions a 
inner join people p on p.peopleid = a.peopleid 
group by p.peopleid 
having max (a.creationdate) < p.alterationdate