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.