SQL. Select names of users who have certain set of skills-CodePudding

There are three tables:

Skills table has the following data:

I need to select names of users, who are skilled in BOTH Node.js AND JavaScript.

So basically I need to select ids of two skills and then filter Users by those skills.

Unfortunately I couldn't come up with a query.

CodePudding user response：

;with E1 as
(
select us.UserId
from UserSkills us
where SkillId in (1, 2)
group by UserId
having count(*) = 2;
)
SELECT Name FROM User AS us
LEFT JOIN E1 AS e ON e.UserId=us.UserId

THATS IT

CodePudding user response：

You can use exists in a query:

with Skillled (UserId) as
(
  select UserId
  from 
  (select distinct us.UserId, us.SkillId 
    from UserSkills us 
    inner join Skills s on us.SkillId = s.Id 
    where s.Name in ('Node.js', 'Javascript')) tmp
  having count(*) = 2 
  group by UserId
)
select Id, Name 
from Users u
where exists (select * from Skilled s where s.userId = u.Id);