Mapping regular SQL to Drupal's Database API - using 'exists'? I have the following SQL that I'm trying to translate using Drupal's Database API:

select nid,biblio_type from biblio where not exists
    (select * from biblio_types where biblio_type = tid);


What would the translation be?  Currently I have this which is not working:

$sub_query = db_select('biblio_types','bt');
$sub_query-&gt;fields('bt',array('tid'))
         -&gt;condition('biblio_type','tid','=');
$query = db_select('biblio','b');
$query-&gt;fields('b',array('biblio_type'))
      -&gt;notExists($sub_query);
$result = $query-&gt;execute();


EDIT #1: Changed exists to notExists.

EDIT #2:

After changing exists to notExists and replacing the condition statement in the subquery with where per Clive's instructions, this is the SQL that is placed in $result-&gt;queryString:

SELECT b.biblio_type AS biblio_type
FROM 
biblio b
WHERE  ( NOT EXISTS  (SELECT bt.tid AS tid
FROM 
biblio_types bt
WHERE  (b.biblio_type = bt.tid) ))


...which is what I expect.  If I place this statement verbatim in MySQL Query Browser I get the exact results I want.  It returns exactly 9 rows of data.  Perfect!
I think you'll need to use a where() for the condition in the subquery, PDO will most likely quote the 'tid' and mess it up.

$sub_query-&gt;where('bt.biblio_type = b.tid');


You can cast a SelectQuery to a string to view it's SQL equivalent at any time, it helps a lot with debugging the more complex queries.

print (string) $query;