Should be an easy select, shouldn't it? 1

[hawkpie]

Sorry if this is an easy one, but it just has me stumped.

I have a table with 2 columns, here's a quick snapshot of the data:

applicantid skillid
387 1
387 2
387 5
387 7
388 1
388 2
388 3
388 5
388 7

I need to write a Select statement that selects all applicant ids for, say, skillids 1 and 3. But they must have them BOTH.

Can't use IN and OR, as this selects people who have at least one of them.

Any pointers would be appreciated.

Cheers
pie

 

[vongrunt]

select applicantid
from myTable
where skillid in (1, 3)
group by applicantid
having count(skillid) = 2

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[hawkpie]

vongrunt, you are an absolute star!!

popped that into the stored procedure and it worked a treat.

cheers
pie