I have a db_select that selects row from t_project_proposal_writer table only if in joined t_project_milestone table row with milestone_id 10 is found:

$query = db_select('t_project_proposal_writer', 'p')->fields('p');
$query->innerJoin('t_project_milestone', 'm', 'p.id=m.proposal_id');
$query->condition('p.initiative_nid', $node->nid)->condition('m.milestone_id', 10);

One project row can have 10 milestone rows. Now I need to do another query that selects only the rows from t_project_proposal_writer where milestone_id 10 not exists in joined t_project_milestone.

Is there any other possiblity to do that, instead of query all the projects and iterate throught them while checking if milestone 10 exists?


You can use isNull() method :

$query = db_select('scald_atoms', 'a');
$query->fields('a', array('sid','title'));
$query->leftjoin('table_name','table_alias','table_alias.entity_id = a.sid');

with your example something like that should do it :

$query->leftjoin('t_project_milestone', 'm', 'p.id=m.proposal_id');
  • It doesn't work - it will select nothing, because for one project are in milestone table 9 rows (with milestone_id from 1 to 9, but not 10). – Incredible Feb 2 '16 at 15:44
  • 2
    Instead the innerJoin do a leftJoin on "p.id=m.proposal_id AND m.milestone_id=10", and then you can check whether the milestone_id is NULL using the method proposed by @Kgaut. – Mario Steinitz Feb 2 '16 at 15:55
  • Good point Mario, Thanks, my copy/paste of the original post was too fast ;). – Kgaut Feb 3 '16 at 7:04

Your Answer

