4

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.

judda
  • 3,977
  • 1
  • 25
  • 27
David Whitten
  • 573
  • 1
  • 4
  • 12

1 Answers1

6
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)
judda
  • 3,977
  • 1
  • 25
  • 27
  • +1 Probably better to materialise the results of the split function into a temp table or #temp table first to avoid evaluating it twice though. – Martin Smith Jun 10 '12 at 18:48
  • Yup ... that is why I swapped it an threw it into a CTE instead of using the dbo.Split ... I just wasn't sure if using SQL Server or not ... though I guess you could use a temp table otherwise. – judda Jun 10 '12 at 18:49
  • 1
    CTEs don't usually materialise the results though in this case the use of `DISTINCT` might mean that SQL Server does add a spool to the plan. – Martin Smith Jun 10 '12 at 18:50