SQL Server, get record which having both skills

Question

I have a employee table and a skill table.

In skills table, I have entries like

empid    skills
---------------
 1       C#
 1       PHP
 2       C#
 2       Java

Now I want all employee id's which have both C# and Java.

Answer 1

One option aggregates by employees and asserts that both C# and Java are present as skills.

SELECT empid
FROM employee
WHERE skills IN ('C#', 'Java')
GROUP BY empid
HAVING COUNT(DISTINCT skills) = 2;

Demo