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. Here is a link to the output:

http://verbclub.com/verb/get/areas

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:

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;
 
}

But it returns nothing. Thanks for your help everyone.

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;
}

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. Here is a link to the output:

http://verbclub.com/verb/get/areas

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:

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;

}

But it returns nothing. Thanks for your help everyone.

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. Here is a link to the output:

http://verbclub.com/verb/get/areas

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:

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;

}

But it returns nothing. Thanks for your help everyone.