How to filter users that have multiple rows where row1="value1" and row2="condition2", ... in SQL?

Question

A have the following data

id user_id visited_country
1  12      Spain 
2  12      France
3  14      England
4  14      France
5  16      Canada
6  14      Spain 
7  16      Mexico

I want to select all users who have visited both Spain and France, excluding those who have visited England. How can I do that in MySQL?

Answer 1

One aggregation approach might be:

SELECT user_id
FROM yourTable
GROUP BY user_id
HAVING SUM(visited_country = 'Spain') > 0 AND
       SUM(visited_country = 'France') > 0 AND
       SUM(visited_country = 'England') = 0;