Return to Answer

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')
    ->join('referential', 'r', 'r.job = j.job AND r.category = j.category');
  $query->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('r.label', $label)
    ->condition('r.activity', $activity);
  $results = $query->execute();

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 query db_select:

// Run your subquery first.
$subquery = db_select('referential', 'r')
  ->fields('r', array('job'))
  ->condition('r.category', 'j.category')
  ->condition('r.label', $label)
  ->condition('r.activity', $activity);
$result = $query->execute()->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')
    ->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('j.job', array_keys($result), 'IN');
  $results = $query->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')
    ->join('referential', 'r', 'r.job = j.job');
  $query->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('r.label', $label)
    ->condition('r.activity', $activity);
  $results = $query->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.

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')
  ->fields('r', array('job'))
  ->condition('r.category', 'j.category')
  ->condition('r.label', $label)
  ->condition('r.activity', $activity);
$result = $query->execute()->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')
    ->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('j.job', array_keys($result), 'IN');
  $results = $query->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')
    ->join('referential', 'r', 'r.job = j.job');
  $query->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('r.label', $label)
    ->condition('r.activity', $activity);
  $results = $query->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.

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 query db_select:

$subquery = db_select('referential', 'r')
  ->fields('r', array('job'))
  ->condition('r.category', 'j.category')
  ->condition('r.label', $label)
  ->condition('r.activity', $activity);
$result = $query->execute()->fetchAllAssoc('job');

if (!empty($result)) {
  $query = db_select('job_orders', 'j')
    ->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('j.job', array_keys($result), 'IN');
  $results = $query->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:

  $query = db_select('job_orders', 'j')
    ->join('referential', 'r', 'r.job = j.job');
  $query->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('r.label', $label)
    ->condition('r.activity', $activity);
  $results = $query->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.

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 query db_select:

// Run your subquery first.
$subquery = db_select('referential', 'r')
  ->fields('r', array('job'))
  ->condition('r.category', 'j.category')
  ->condition('r.label', $label)
  ->condition('r.activity', $activity);
$result = $query->execute()->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')
    ->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('j.job', array_keys($result), 'IN');
  $results = $query->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')
    ->join('referential', 'r', 'r.job = j.job');
  $query->fields('j')
    ->condition('j.statut', 'To do')
    ->condition('r.label', $label)
    ->condition('r.activity', $activity);
  $results = $query->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.