CODEWITHSUNDEEP
mysql
Anoop P S
Anoop P S

Reputation: 782

How can I fetch user id which have two values

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
---------------------------------------

Upvotes: 0

Views: 390

Answers (2)

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133370

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

Upvotes: 0

Related Questions