mysql how to select years when all three given countries participated?

Question

I have a table with columns year and country.

+------+-----------------
| year | country
+------+-----------------
| 1957 | The Netherlands
| 1957 | Belgium
| 1957 | Luxembourg
| 1954 | Hungary
| 1954 | Belgium
| 1932 | USA
+------+-----------------

I am trying to list the years when Luxembourg AND The Netherlands AND Belgium participated. I should get 1957 but I don't know how to do that in mysql.

Answer 1

You can group by country and set a condition in the HAVING clause:

select year 
from tablename 
where country in ('Luxembourg', 'The Netherlands', 'Belgium')
group by year
having count(distinct country) = 3

You may remove distinct if there is no case of duplicate combinations of year and country.
See the demo.
Results:

| year |
| ---- |
| 1957 |