How can I fetch user id which have two values

Question

For example, I have 2 tables skill table and user table

I need to fetch the only user_id from table user who has both skill_id 1041 and 47.

How to fetch when inner join where job_id =39.

Skill table

---------------------------------------
| id | job_id | skill_id | profile
---------------------------------------
| 1 |   39   |  1041    | intermediate
---------------------------------------
| 2 |   39   |  47      | intermediate
---------------------------------------

User table

---------------------------------------
| id | user_id | skill_id | profile 
---------------------------------------
| 1 |   212   |  1041    | intermediate
---------------------------------------
| 2 |   212   |  1218    | intermediate
---------------------------------------
| 3 |   213   |  1041    | intermediate
---------------------------------------
| 4 |   213   |  47      | intermediate
---------------------------------------

i think this is what though SELECT * FROM `user` WHERE skill_id = 1041 and skill_id=47. this is user is a mapping table i need to get user who have both skill — Anoop P S, Dec 24 '18 at 11:00
Possible duplicate of [Finding duplicate values in MySQL](https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql) — Mark Amery, Dec 24 '18 at 13:51

score 3 · Accepted Answer · answered Dec 24 '18 at 11:01

3

You can try below -

 select user_id,count(skill_id) from user_table
    where skill_id  in (1041,47)
    group by user_id
    having count(skill_id)=2

answered Dec 24 '18 at 11:01

Fahmi

37,315
5
22
31

score 0 · Answer 2 · answered Dec 24 '18 at 10:59

0

you could use the skill table two time in join

select s1.user_id 
from skill s1  
inner join skill s2 on s1.user_id = s2.user_id 
    and s1.skill_id =1014 
    and s2.skill_id = 47

answered Dec 24 '18 at 10:59

ScaisEdge

131,976
10
91
107

and wont work even in user table alone hope this is what u mean SELECT * FROM `jh_job_user_skill` WHERE skill_id = 1041 and skill_id=47 – Anoop P S Dec 24 '18 at 11:03

How can I fetch user id which have two values

2 Answers2