<:avglsdata id=avglsdata> Please, this should not be in development; it belongs in support.
Actually, Ivan's query won't work because each skill is on a separate row. Your query, AFAICT, is the only way the way to actually find multiple skill combinations. It also has the advantage of scaling to handle even more skills. But let's make it truly Drupalized:
SELECT e.employee_id FROM {employees} e WHERE EXISTS(SELECT 1 FROM {skills} s WHERE s.employee_id = e.employee_id AND s.skill_id = 'java') AND EXISTS(select 1 FROM {skills} s WHERE s.employee_id = e.employee_id AND s.skill_id = 'C++')
Of course, to use it in a db_query(), you will need to further parameterize it.
$skills_desired = array('java', 'c++', 'php'); // Values can be filled however you prefer. $wheres = array_fill(0, count($skills_desired), "EXISTS(SELECT 1 FROM {skills} s WHERE s.employee_id = e.employee_id AND s.skill_id = '%s')"); $query = "SELECT e.employee_id FROM {employees} e WHERE " . implode(' AND ', $wheres); if ($result = db_query($query, $skills_desired)) { while ($result = db_result($result)) { // ... do something with the employee. } } else { return t('No matches found.'); } This is, of course, Drupal 6 code. DBTNG can do this more efficiently.
Nancy Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
________________________________
From: David Metzler Select * form employees e where exists (select 1 from skills s where s.employee_id=e.employee_id and s.skill_id = 'java') AND exists(select 1 from skills s where s.employee_id=e.employee_id and s.skill_id='C++')
I have heard that mysql will perform better with Ivan's query, but I haven't verified that this is the case.