MySQL - Hard Query with Max, Datediff, Subquery, Distinct/Limit

Question

In short: MySQL - I need to bring company that have been inactive for a while (or 365 days for the fiddle example).

How I check this? each company have at least a contact, who is related to an event, and each event have (many) subevents, in this last table I have the last date of activity, the days that considers that one company is on inactivity is decided for the user, I don't have problem to do this calculation

sql.Append("where DATEDIFF(CURDATE(),DATE(lastdate)) > " +days.ToString()+ "

The problem is, that this check ALL the subevents, so this not only check the last date, but every date... and this means, bad output.

I was thinking on subqueries to get or the max date on the subevent of a contact, or the max date of the subevent of a event.

Then with a friend we get close with sort of this, but the query is infinite.

select * from subevent se
where DATEDIFF(CURDATE(),DATE(
(select se2.dates from subevent se2 
where  se2.dates in 
(select max(se3.dates) 
from subevent se3 
where se.idev = se3.idev) 
group by se2.dates)));

I'm stuck and I would appreciate the help...

Tried group by, subquery and MAX (obviously max is necessary, but don't how where to apply...)

https://www.db-fiddle.com/f/wgSQGn7Z26tHnwm6nAaNSA/8

(On the Fiddle link, should only bring the companyname2 and companyname4)

Answer 1

You can use aggregation to get the last subevent date for each company. Then filter using a having clause:

select c.idcomp
from contact c join
     events e
     on e.idcont = c.idcont join
     subevent se
     on se.idev = e.idev
group by c.idcomp
having max(se.date) < current_date - interval 365 day;

Here is a db-fiddle.