Mapping regular SQL to Drupal's Database API - using 'exists'?

Question

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->fields('bt',array('tid'))
         ->condition('biblio_type','tid','=');
$query = db_select('biblio','b');
$query->fields('b',array('biblio_type'))
      ->notExists($sub_query);
$result = $query->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->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!

Answer 1

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->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;