SQL - selection of all skills

Question

some time has passed since I used SQL, so I apologize if this is too easy. I have to select all the skills that the user has, so I have three tables.

User (id, name) Skills (id, name) User_skills (id_user, id_skill) 

If user1 has 2 skills; for example, Hibernate (id 1 ) and Java (id 2 ) and user2 have 1 skill; Java (id 1 )
By passing 1 and 2 , I want to get users who have both.

Using the IN () function, I get all users who have at least one of the skills, but I want to filter them out!
Thank you all in advance

Answer 1

If one skill can only be assigned once to a user (i.e. (id_user, id_skill) is PK for the user_skills table), then the following will do what you want:

 SELECT id_user FROM user_skills WHERE id_skill IN (1,2) GROUP BY id_user HAVING count(*) = 2 

Answer 2

Join the user_skills association table user_skills by placing the skill identifier in the on clause of each connection:

 select u.* from user u join user_skills us1 on us1.id_user = u.id and us1.id_skill = 1 join user_skills us2 on us2.id_user = u.id and us2.id_skill = 2 

Using join (rather than left join ), this request requires the user to have skills

Answer 3

 SELECT name FROM user as u WHERE EXISTS( SELECT 1 FROM User_skills WHERE id_user=u.id AND id_skill=1 ) AND EXISTS( SELECT 1 FROM User_skills WHERE id_user=u.id AND id_skill=2 )