Database query with notExists

Question

I can't figure out how to select all aid's from the areas table that do not exist in the field_data_field_area_parent table.

It almost works, except it returns Windy Ridge with an "aid" of "11". I am guessing that this is because "IN" is the same as "ANY", and the number "1" is in the subquery results.

How can I use NOT EXISTS instead of NOT IN?

function verb_get_parent_areas () {
  $query = db_select('areas', 'a');
  $query->distinct();
  $query->fields('a',array('name','aid','parent','latitude','longitude','description'));
  $subquery = db_select('field_data_field_area_parent', 'p');
  $subquery->fields('p', array('entity_id'));
  $query->condition('a.aid', $subquery, 'NOT IN');

  $result = $query->execute();
  while ($record = $result->fetchAll()) {       
    $areas = drupal_json_encode($record);
  }

  return $areas;
  exit;
}

I've thought of trying this, but it returns nothing.

function verb_get_parent_areas () {
  $query = db_select('areas', 'a');
  $query->distinct();
  $query->fields('a',array('name','aid','parent','latitude','longitude','description'));
  $subquery = db_select('field_data_field_area_parent', 'p');
  $subquery->fields('p', array('entity_id'));
  $query->notExists($subquery);

  $result = $query->execute();
  while ($record = $result->fetchAll()) {       
    $areas = drupal_json_encode($record);
  }

  return $areas;
  exit;
}

Answer 1

Here is another approach to write Dynamic queries. We generally write queries in phpmyadmin or mysql command prompt and then convert them to D7 style or dynamic queries using Query coder module.

For example, as per your question, you want to find out all those records in child table which don't exist in parent table.

So D6 style query,

db_query("SELECT c.* FROM {child} c LEFT JOIN {parent} p ON c.p_id = p.p_id WHERE p.p_id IS NULL");

after converting to D7 style query using query coder module would look like,

$query = db_select('child', 'c');
$query->fields('c', array('*'));
$query->leftJoin('parent', 'p', 'c.p_id = p.p_id');
$query->isNull('p.p_id');
$result = $query->execute();

Answer 2

Have you tried out drupal_write_record() function? if no then see below link,

https://stackoverflow.com/questions/5825337/help-with-db-query-in-drupal-if-exists-update-else-insert

This may helpful to you.

Thanks.