Home > Software engineering >  How to select multiple rows of the same user that satisfies some condition in SQL?
How to select multiple rows of the same user that satisfies some condition in SQL?

Time:05-05

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;
  • Related