How to join collections in Magento?

collections, grid, join, magento

Solution

Here you have a working example:

$collection = Mage::getModel('sales/order')->getCollection();
$collection->getSelect()->join( array('order_item'=> 'sales_flat_order_item'), 'order_item.order_id = main_table.entity_id', array('order_item.sku'));

Problem

I am trying to join a custom collection with products to show the product name (not just the id) in the admin grid widget. So far i can't find the correct syntax. I am able to retrieve the products with the product name by the following: Mage::getModel('catalog/product')->getCollection()->addAttributeToSelect('name'); and i can retrieve my custom collection with: Mage::getResourceModel('xyz_mymodule/model_collection'); How do i join the two so that the module collection is the primary collection and the id as returned by $model->getId() is still the id of my custom collection?

Original source