How to filter users that have multiple rows where row1="value1" and row2="condition2&-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

One way:

select user_id
from test_tbl tt
where exists ( select 1 from test_tbl t1 
               where t1.user_id=tt.user_id 
               and visited_country in ('Spain','France')
              )
and not exists ( select 1 from test_tbl t2 
                 where t2.user_id=tt.user_id 
                 and visited_country  in ('England')
              )
 group by user_id;

https://www.db-fiddle.com/f/pdmSebvUJVN6sBidyAUimR/1