SQL - Select all skills

Question

it's been a while since I used SQL so I'm asking sorry if it's too easy. I have to select all the skills that a user has, so I have three tables.

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

If the user1 has 2 skills; for example Hibernate (id 1) and Java (id 2) and the user2 has 1 skill; Java (id 1)
Passing 1 and 2, I want to retrieve users that have both.

With the IN() function I get all the users that have at least one of the skills, but I want to filter them out!
Thanks to all in advance

Answer 1

If one skill can only be assigned exactly once to a user (i.e. (id_user, id_skill) is the 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 to the association table user_skills twice, putting the skill ID in the on clause of each join:

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

By using join (and not left join) this query requires the user have both skills