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