Re-writing SQL statement in Drupal dynamic queries I have the following SQL query, which I need to re-write using Drupal's dynamic query language. I'm new to Drupal development, could you please give me an example for the following SQL?

SELECT 
    node.title, 
    GROUP_CONCAT(field_data_field_location.field_location_value) 
FROM 
    node 
    INNER JOIN field_data_field_location 
        ON node.nid = field_data_field_location.entity_id 
    INNER JOIN 
        (SELECT DISTINCT 
            entity_id 
         FROM 
            field_data_field_location 
         WHERE 
            field_location_value IN ('Berlin', 'Stuttgart')) AS IDS
        ON node.nid = IDS.entity_id
GROUP BY 
    node.nid




What I currently have is the following:

$query = db_select('node', 'n');
$query-&gt;addField('n', 'title', 'title');
$query-&gt;groupBy('n.nid');

$subquery = db_select('field_data_field_location', 'loc');
$subquery-&gt;distinct();
$subquery-&gt;fields('loc.entity_id', array('eid'));
$subquery-&gt;condition('loc.field_location_value', $locations, 'IN');

$query-&gt;join('field_data_field_location', 'l', 'n.nid = l.entity_id');
$query-&gt;addExpression('GROUP_CONCAT(l.field_location_value)', 'locations');
$query-&gt;addJoin('INNER', $subquery, 'IDS', 'n.nid = IDS.entity_id');

$result = $query-&gt;execute();

return $result-&gt;fetchAll();


If I execute the code, I just get nothing back from Drupal. I think the problem is the subquery. Do you have any idea how I can solve that?
Have you turned down the error_level? because if you get nothing back from Drupal then this typically means the query executed successfully but that no results were found. Perhaps check the Reports-&gt;Recent log messages for any SQL errors logged instead. This information should help you troubleshoot future Drupal SQL endeavours.

Anyway, looking over the code, I don't think this is correct:

$subquery-&gt;fields('loc.entity_id', array('eid'));


and should probably be:

$subquery-&gt;fields('loc', array('entity_id'));


or if you want to give the field an alias, use addField:

$subquery-&gt;addfield('loc', 'entity_id', 'eid');


I've just run a similar query on a fresh database (so no custom fields) and set that fields() call correctly, and this seems to get results back:

$bundles = array("page", "blog");

$query = db_select('node', 'n');
$query-&gt;addField('n', 'title', 'title');
$query-&gt;groupBy('n.nid');

$subquery = db_select('field_data_body', 'data');
$subquery-&gt;distinct();
$subquery = $subquery-&gt;fields('data', array('entity_id'));
$subquery = $subquery-&gt;condition('data.bundle', $bundles, 'IN');

$query-&gt;join('field_revision_body', 'rev', 'n.nid = rev.entity_id');
$query-&gt;addExpression('GROUP_CONCAT(rev.bundle)', 'mybundles');
$query-&gt;addJoin('INNER', $subquery, 'IDS', 'n.nid = IDS.entity_id');

$result = $query-&gt;execute();

dpq($query); // Let's look at the query
dpm($result-&gt;fetchAll()); // Let's see all the results


Note, you'll need the devel module installed to use the dpm() and dpq() functions. Hope that can help!