Sql IN function

Question

I have something like this as an sql table:

EmplID  SkillID
1780      128
1780      133
2061      128
2068      128

Lets say I pass in a string to a stored proc as in '128,133'. I have a split function so the sql might be like the following:

SELECT DISTINCT EmplID
FROM EmplSkills
WHERE SkillID IN(SELECT data FROM dbo.Split(@Skills, ','))

You can easily see that by the sql statement we will get 1780, 2061, and 2068. What I really want is to get those EmplID's where they have both 128 and 133 as Skill Id's so with that I should only get 1780.

Any ideas how to approach this without a bunch of crazy joins?

Thanks so much.

Answer 1

SELECT EmplID
FROM EmplSkills
WHERE SkillID IN(SELECT data FROM dbo.Split(@Skills, ','))
GROUP BY EmplID
HAVING COUNT(DISTINCT SkillID) = (SELECT COUNT(*) FROM dbo.Split(@Skills, ','))

Or, if you are using SQL Server, you could use a CTE to make life easier.

; WITH Skills AS (
    SELECT DISTINCT data
    FROM dbo.Split(@Skills, ',')
)
SELECT EmplID
FROM EmplSkills
WHERE SkillID IN ( SELECT data FROM Skills )
GROUP BY EmplID
HAVING COUNT(DISTINCT SkillID) = (SELECT COUNT(*) FROM Skills)