selecting records limited by only those with both criteria met

Question

OK, the title is confusing but i will explain. Say i have a employee skills db for which i would like managers to be able to search through.

A manager may want to search for employees with nunchuck skills... ok great, thats easy: SELECT * FROM skilllist WHERE skillname= "nunchuck"

Bam! I got a list of employees with nunchuck skills.

But say a manager would like to get a list of employees with both "nunchuck" skills and "bow staff" skills. How would i create a query like that? I cant just say:

SELECT * FROM skilllist WHERE skillname= "nunchuck" AND skillname= "bow staff"

or

SELECT * FROM skilllist WHERE skillname= "nunchuck" OR skillname= "bow staff"

The latter would just return all employees with nunchuck skills and all employees with bow staff skills. I would like to get ONLY employees with both skills. I cant figure it out. Please help! :)

Answer 1

EDITED From clearly incorrect first response where I was too speedy in reading the post.

You could group by employee and count, where the count = the number of skills your looking for. This assumes there's no skill duplication. This is the syntax I would use for MSSQL, so might be slightly different for MySQL.

SELECT [EmployeeName], Count(1) FROM 
skilllist 
WHERE skillname in ( "nunchuck","bow staff")
Group By [EmployeeName]
Having Count(1) = 2

Answer 2

Yes, I agree with Smandoli.

But then you have to compare the id's and expand your Query-Statement with an AND clause:

SELECT * FROM skilllist WHERE skillname= "nunchuck" AND skillname= "bow staff" AND table1.id=table2.id

Answer 3

This could work:

Select *
  from employees
 where 1 < (select count(*) 
              from skilllist 
             where slilllist.emp_id = employees.emp_id 
               and skillname in ('nunchuck','bow staff') )

However, it hard codes the amount of skills. So if you now added a third choice you would have to increase the 1 to 2.

Answer 4

select * from employees where 
employeeID in (select memberID from skilllist where skillname = 'nunchuck') and 
employeeID in (select memberID from skilllist where skillname = 'bow staff')

Answer 5

Select * from employees
RIGHT JOIN skilllist s1 ON s1.memberid = employees.id AND s1.skillname = "nunchuck"
RIGHT JOIN skilllist s2 ON s2.memberid  = employees.id AND s2.skillname = "bow staff"

...