Can anybody suggest to me how I can write a dynamic query in Drupal for the below MySQL query. Any suggestion will be appreciated.
select * from cap_pas where pas_uid not in (select pas_uid from cap_boat_to_pas)
There are many similar questions on Drupal Answers.
$subquery = db_select('cap_boat_to_pas', 'cp') ->fields('cp', array('pas_uid')); $query = db_select('cap_pas', 'cp') ->fields('cp') ->condition('cp.pas_uid', $subquery, 'NOT IN'); results = $query->execute()->fetchAll();
Note: to write this in a single query (by using joins), take a look at leftJoin().