Zend Select NOT IN

I have two tables with related data, and I want to select all the records from one table which do not exist in 开发者_如何学运维the other table, plus some other criteria on the related table, as follows (123 is just for illustration purposes):

TABLE A
    ID
    SOMETHING

TABLE B
    TABLE_A_ID
    TABLE_C_ID
    SOMETHING

My query, run directly against the data, would be as follows

SELECT A.SOMETHING 
    FROM A 
    WHERE A.ID NOT IN (
        SELECT 
            B.TABLE_A_ID AS ID 
            FROM B 
            WHERE TABLE_C_ID = 123
    );

How can I run this in Zend?

You can run direct sql, using $db->query(); yours would simply be:

$results = $db->query("SELECT A.SOMETHING 
FROM A 
WHERE A.ID NOT IN (
    SELECT 
        B.TABLE_A_ID AS ID 
        FROM B 
        WHERE TABLE_C_ID = ?
)", $id);

EDIT: To answer whether this can be done with the object notation, yes:

$sub_select = $zdb->select()
                  ->from("b", array("table_a_id AS id"))
                  ->where("table_c_id = ?", 'a');
$select = $zdb->select()
              ->from("a", array("something"))
              ->where("id NOT IN ?", $sub_select);
print $select->__toString();

gives

SELECT `a`.`something` FROM `a`
WHERE (id NOT IN
    (SELECT `b`.`table_a_id` AS `id` FROM `b` WHERE (table_c_id = 'a')))