db_select with subquery including conditions from 2 tables I spent much time without success to search how to convert with db_select the following query, running perfectly with db_query :

    SELECT *
    FROM job_orders j
    WHERE j.statut = 'To do'
    AND j.job
    IN ( SELECT r.job
    FROM referential r
    WHERE r.category = j.category
    AND r.label = :label
    AND r.activity = :activity ),
    array(':label' =&gt; $label, ':activity' =&gt; $activity);


Here is the corresponding (?) db_select query I wrote :

$subquery = db_select('referential', 'r')
    -&gt;fields('r', array('job'))
    -&gt;condition('r.category', 'j.category')
    -&gt;condition('r.label', $label)
    -&gt;condition('r.activity', $activity);

$query = db_select('job_orders', 'j')
    -&gt;fields('j')
    -&gt;condition('j.statut', 'To do')
    -&gt;condition('j.job', $subquery, 'IN');
$results = $query-&gt;execute();


I got no result with this db_select query, while I got answers with the db_query one... could somebody tell me what is wrong in my db_select query ?

Thank you.
To achieve your desired behavior you have two options.

If you really like to filter by using a subquery, you should execute it first, parse its result set to an array and set this array as parameter to the condition method of your second queries db_select:

// Run your subquery first.
$subquery = db_select('referential', 'r')
  -&gt;fields('r', array('job'))
  -&gt;condition('r.category', 'j.category')
  -&gt;condition('r.label', $label)
  -&gt;condition('r.activity', $activity);
$result = $query-&gt;execute()-&gt;fetchAllAssoc('job');

// Make sure $results is initialized for further evaluation.
$results = array();

// If we have referential results, execute the query on job orders.
if (!empty($result)) {
  $query = db_select('job_orders', 'j')
    -&gt;fields('j')
    -&gt;condition('j.statut', 'To do')
    -&gt;condition('j.job', array_keys($result), 'IN');
  $results = $query-&gt;execute();
}


The far more elegant and therefore suggested solution is to using an INNER JOIN on your query. The target query would look like:

SELECT j.*
  FROM job_orders j
  INNER JOIN referential r ON (r.job = j.job)
  WHERE j.statut = 'To do' AND r.label = :label AND r.activity = :activity


You can achieve this solution using db_select by adding a join to your $query:

  // Run the query using an INNER JOIN.
  $query = db_select('job_orders', 'j')
    -&gt;join('referential', 'r', 'r.job = j.job');
  $query-&gt;fields('j')
    -&gt;condition('j.statut', 'To do')
    -&gt;condition('r.label', $label)
    -&gt;condition('r.activity', $activity);
  $results = $query-&gt;execute();


Edit:
As I don't see any special reason for using db_select in your use case, I'd also suggest on sticking to db_query here for better performance of your application.

Edit 2:
In the above 'solutions' I have overseen you are using two conditions between the tables. In this case, you have to add both conditions to your JOIN:

SELECT j.*
  FROM job_orders j
  INNER JOIN referential r ON (r.job = j.job AND r.category = j.category)
  WHERE j.statut = 'To do' AND r.label = :label AND r.activity = :activity


Which would need the following db_select:

  $query = db_select('job_orders', 'j')
    -&gt;join('referential', 'r', 'r.job = j.job AND r.category = j.category');
  $query-&gt;fields('j')
    -&gt;condition('j.statut', 'To do')
    -&gt;condition('r.label', $label)
    -&gt;condition('r.activity', $activity);
  $results = $query-&gt;execute();