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')
  ->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().

  • Thanks for your suggestion. Is there any possibility of writing this in a single query by using joins?? – Suraj Jun 7 '16 at 4:23
  • Yes. you can. I didn't try it. You can take a look on leftJoin() – Jimmy Ko Jun 7 '16 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.