CODEWITHSUNDEEP
mysqlsql
Aman
Aman

Reputation: 2431

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

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

Answers (2)

user3369545
user3369545

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

Stu
Stu

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

Related Questions