[support] [development] SQL Query Doubt

[nan wich]

<: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. 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110416/43397103/attachment.html