Reputation: 2431
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?
Upvotes: 1
Views: 50
Reputation: 421
@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 not exists
(Select distinct user_id
From table where visited_country in ('England'))
group by User_Id
having Count(distinct visited_country)> = 2;
Upvotes: 0
Reputation: 32614
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;
Upvotes: 1