limit db_select query by the value of a specific field I am trying to build a custom drupal module to return the 'staff' that work at a specific charity when you are on the page of the charity. Both the charities and the staff members exist as separate content types. These content types are updated dynamically from a CRM system. The charities and the staff members both have a common field 'Account ID'. 

I can get the account id of the charity whose page we are viewing:

    //Get Account ID of Charity for page we are on
    $charity_account_id = $node-&gt;field_member_account_id[LANGUAGE_NONE][0]['value'];    


I can query the database and return only the contacts of the staff members type ('salesforce_contacts') and optionally limit it by the title of the returned results (e.g: n.title','Abouzeid')

    //Use Database API to retrieve the appropriate contact(s) 
      $query = db_select('node', 'n')
        -&gt;fields('n', array('nid', 'title', 'created'))
        -&gt;condition('status', 1) //Published.
        -&gt;condition('type', 'salesforce_contacts') // content type is salesforce_contacts.
        -&gt;condition('n.title','Abouzeid','=') // I can limit the returned results by a specific title
        -&gt;orderBy('title', 'ASC') //Most recent first.
        -&gt;execute();
      return $query; 


I need to know how to limit the returned results by the value of a specific cck field from the staff member content type ('salesforce_contacts'). This will allow me just to return the contacts for the charity we are looking at.

I have tried adding the drupal machine name for the field (or the mySQL database table name) to the field array as in below but both of these throw a PDOException error. 

      -&gt;fields('n', array('nid', 'title', 'created', 'field_account_id'))


I would really appreciate some help with this. This is my first Drupal database query and I am pretty new to DB queries in general. I have tried reading through the documentation but am struggling to where to look to get ideas as to how to go about achieving what I need to.

Thanks.
Scott's answer and Triskelion's comment are both spot on, but there are also a couple of other solutions that are worth mentioning.


Query the database for some node IDs that match a condition, load them using node_load_multiple(), and inspect the field data:

$nids = db_select('node', 'n')
  -&gt;fields('n', array('nid'))
  -&gt;condition('status', 1)
  -&gt;condition('type', 'salesforce_contacts')
  -&gt;condition('n.title', 'Abouzeid')
  -&gt;orderBy('title', 'ASC')
  -&gt;execute();

$nodes = node_load_multiple($nids);

foreach ($nodes as $node) {
  $title = $node-&gt;title;
  $created = $node-&gt;created;
  // etc.

  if ($field_items = field_get_items('node', $node, 'field_account_id')) {
    $account_id = $field_items[0]['value'];
  }
}

Use an EntityFieldQuery to query the nodes. This method has the advantage of being able to add conditions based on fields, as well as just the columns available in the node table.

$query = new EntityFieldQuery();
$query-&gt;entityCondition('entity_type', 'node')
  -&gt;propertyCondition('type', 'salesforce_contacts')
  -&gt;propertyCondition('status', 1)
  -&gt;propertyCondition('title', 'Abouzeid')
  -&gt;propertyOrderBy('title')

$results = $query-&gt;execute();
if (!empty($results['node'])) {
  $nodes = node_load_multiple(array_keys($results['node']));

  // Loop through the nodes as in the above example.
}