CODEWITHSUNDEEP
mysqlsqlselect
cpat21
cpat21

Reputation: 21

How to select users that meet 2 requirements from the same column

I am trying to answer the following question. I need to select users who have listened to both track 1 and track 2 in the Track column. For some reason I am completely lost in how to do this.

Upvotes: 1

Views: 72

Answers (5)

Buddhi
Buddhi

Reputation: 421

This works?

SELECT UserId
FROM YourTable
WHERE Track in ('track_1','track_2')
group by UserId
Having  count(distinct Track) > 1;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311163

You could count the number of relevant tracks each user listened to:

SELECT   user_id
FROM     mytable
WHERE    track IN ('track_1', 'track_2')
GROUP BY user_id
HAVING   COUNT(DISTINCT track) = 2

Upvotes: 3

Twelfth
Twelfth

Reputation: 7180

Can probably go more simplistic than needing a where exists subquery.

Select userID
from yourtable
where Track in ('Track 1','Track 2')
group by userid
having count(1) = 2

Upvotes: 0

kjmerf
kjmerf

Reputation: 4335

If there is only two you could also use GROUP BY and HAVING:

SELECT user_id
FROM t
GROUP BY user_id
HAVING COUNT(DISTINCT track) > 1

Upvotes: 0

Darzen
Darzen

Reputation: 1135

Here's one of the ways:

SELECT user_id from TABLE_NAME 
WHERE track = 'track_1' AND 
           user_id IN 
           (SELECT user_id from TABLE_NAME where track = 'track_2');

Upvotes: 1

Related Questions