Convert a subquery to Zend DB

Question

I want to turn the SQL below into a Zend Db query.

Select * from catalog_product_entity where entity_id not in ( select Distinct(product_id) from sales_flat_order_item)

Can anyone help me with this, I am currently new to magento and it still confuse me. Any help is appreciated.

Answer 1

Try this:

    $collection = Mage::getResourceModel('catalog/product_collection');

    $subQuery = $collection->getConnection()->select()
        ->from($collection->getTable('sales/order_item'), 'product_id')
        ->distinct(true);

    $collection->getSelect()
        ->reset(Zend_Db_Select::COLUMNS)
        ->columns('e.entity_id')
        ->where('e.entity_id NOT IN (?)', $subQuery);