How can I write dynamic query?
There are many similar questions on Drupal Answers.

$subquery = db_select('cap_boat_to_pas', 'cp')
  -&gt;fields('cp', array('pas_uid'));

$query = db_select('cap_pas', 'cp')
  -&gt;fields('cp')
  -&gt;condition('cp.pas_uid', $subquery, 'NOT IN');

results = $query-&gt;execute()-&gt;fetchAll();


Note: to write this in a single query (by using joins), take a look at leftJoin().