CODEWITHSUNDEEP
mysqlsql
Rajesh Vishwakarma
Rajesh Vishwakarma

Reputation: 387

How user mysql IN query like AND condition

I have a table with 2 columns user_id, skills_id. A user have multiple skills with new row. I have to find those users who have same skills_id.

user_id skills_id
      1         6
     10         5
     11         2
     11         3
     15         2
     15         4
     15         3
     17         2
     18         2
     18         3
     18         4
     20         2 <-- I have to find users who have values of both 2 and 4 for skill_id. 
     20         4 <-- So user 15 in this example.

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

You need to find other users with the same skills. Assuming the skills are not duplicated, here is one approach:

select t.user_id
from table t join
     table t2
     on t.skills_id = t2.skills_id and
        t2.user_id = 20
group by t.user_id
having count(*) = (select count(*) from table t3 where t3.user_id = 20);

Note that this will return user 20 as well as user 15. You can filter that out if you like.

If the skills can be duplicated for a user, you would just use count(distinct) instead.

Upvotes: 1

Jens
Jens

Reputation: 69470

Try this untested query:

Select user_id from `table` where skills_id in (2,4) group by user_id having count(*) =2

Upvotes: 1

Related Questions