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
I want to select all users who have visited both Spain and France. How can I do that in MySQL?
CodePudding user response:
Something like the following should suffice:
select user_Id
from t
where visited_country in ('Spain','France')
group by User_Id
having Count(distinct visited_country) = 2;
CodePudding user response:
@Aman: If you are trying to exclude England then it would be
select distinct user_Id
from table
where visited_country in ('Spain','France')
and visited_country not in ('England')
group by User_Id
having Count(distinct visited_country)> = 2;