mysql - Select all where exists in specific years but not in others specific years

Question

I have a table with two columns, ID and YEARS. Years starts from 2000 to present. Some ID appears 20 times in ID Column (on for every year), others only one time (for 2020 year). I want to select those ID that exists in specific years but not in others specific years. Ex: Select those where exists in 2013 and 2107 but not in years 2015 and 2018.

Answer 1

You want to aggregate the table to get one row per ID (GROUP BY id). You only want those IDs that exist in particalar years and not in other particular years.

Conditions on aggregations are placed in HAVING. In MySQL true equals 1 and false equals 0, so you can just add them up:

select id
from mytable
group by id
having sum(year = 2013) > 0
   and sum(year = 2017) > 0
   and sum(year = 2015) = 0
   and sum(year = 2018) = 0
order by id;