First of all I'm sorry if this question has been asked before, I really tried searching but couldn't find on what I was looking for.
Okay so this is my table. I want to have a query where I select the user id's who have the same exact value more then 2 times.
| USER ID|COLOR|
---------|-------
| 1 | BLUE
| 2 | BLUE
| 3 | RED
| 4 | BLUE
So my query in this case would return
| USER ID|
|--------|
| 1 |
| 2 |
| 4 |
Thank you very much!
You seem to want to select the rows where color is repeated more than twice. Here is one method:
select t.*
from t join
(select color, count(*) as cnt
from t
group by color
) tt
on tt.color = t.color and cnt > 2;
You asked for more than 2 matches. For 2 or more matches, you would use sgeddes's solution:
select t.*
from t
where exists (select 1
from t tt
where t.color = tt.color and t.userid <> tt.userid
);
Or, if you just want the multiple ids:
select color, group_concat(userid)
from t
group by color
having count(*) >= 2;
Here's one option using exists:
select userid
from yourtable t
where exists (
select 1
from yourtable t2
where t.userid <> t2.userid and t.color = t2.color
)
