CODEWITHSUNDEEP
sql
ianaz
ianaz

Reputation: 2580

SQL - Select all skills

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

Upvotes: 4

Views: 354

Answers (3)

VolkerK
VolkerK

Reputation: 96159

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 )

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 8

Bohemian
Bohemian

Reputation: 425198

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

Upvotes: 3

Related Questions