How to do join on Drupal dynamic query

Question

Following the documentation on https://www.drupal.org/docs/7/api/database-api/dynamic-queries/joins I have constructed a very simple dynamic query with join:

$query = db_select('node', 'n')
     ->condition('n.uid', $promoter, '=')
     ->condition('n.type', 'campaign', '=')
     ->condition('n.status', '1', '=')
     ->fields('n', array('nid'));
     $query->join('field_data_field_department', 'd', 'n.nid = d.entity_id');

  $result = $query->execute();
    foreach ($result as $record) {
      debug($record);
}

and it only returns nids whereas the expected results were nids together with the field_data_field_department field values. What should the correct query.

Answer 1

You're only selecting the nid field, you just need some more:

...
->fields('n', array('nid'))
->fields('d', array('fields', 'you', 'want'));