How can I add a subquery in JOIN

Question

I want to make a join like

SELECT * FROM catalog_product_entity as c
 INNER JOIN catalog_product_entity_varchar AS cv ON (c.entity_id = cv.entity_id) and(cv.attribute_id = 
 (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name'))

How can I add this in a join in magento with collection?

Answer 1

For this specific query:

$collection = Mage::getResourceModel('catalog/product_collection');
$collection->addAttributeToSelect('name');

If you want to have more control over the subquery, the following question should help you:

• Subquery in a collection