Search code examples
mysqlsqlrelational-division

Using MySQL, how can I tell who has attended at least one event every year?

I have a table with name column and date column. I've had events since 2006. How can I get a list of names that have attended at least one event per year from 2006 to 2017?

Also, instead of hardcoding 2017, how can I use the most recent year that appears in the database?

Solution

  • You can do this as:

    select name
from t
group by name
having count(distinct year(date)) = (select count(distinct year(date))
                                     from t
                                    );

    This selects users that have attended events in all years for which you have event records.

    If you specifically wanted all years -- even the current year if there are no events, then:

    select name
from t
group by name
having count(distinct year(date)) = (select 1 + year(curdate()) - year(min(date)) + 1
                                     from t
                                    );