displaying data from two tables in one grid

Question

I have a grid for my admin module and it works fine, but I now would like to add more data onto that grid, but the additional data is held in a second table in my database.

To display this additional data I have tried

protected function _prepareCollection()
{
 $collection = Mage::getModel('modulename/modelname')->getCollection();
 $collection->join(array('table2' => 'table1'), 'table1_id = table2_id', 'unsubscribed');//additional line
 $this->setCollection($collection);
 return parent::_prepareCollection();
}
protected function _prepareColumns()
{
 $this->addColumn('table1_id', array(
 'header' => $this->__('ID'),
 'align' => 'left',
 'width' => '50px',
 'index' => 'table1_id',
 ));
 $this->addColumn('salutation', array(
 'header' => $this->__('Title'),
 'align' => 'left',
 'index' => 'salutation',
 'width' => '80px',
 ));
 $this->addColumn('f_name', array(
 'header' => $this->__('First Name'),
 'align' => 'left',
 'index' => 'f_name',
 'width' => '100px',
 ));
 $this->addColumn('l_name', array(
 'header' => $this->__('Last Name'),
 'align' => 'left',
 'index' => 'l_name',
 'width' => '100px',
 ));
 $this->addColumn('subscribed', array(
 'header' => $this->__('Subscribed'),
 'align' => 'left',
 'width' => '80px',
 'index' => 'Subscribed',
 'type' => 'options',
 'options' => array(
 0 => 'Subscribed',
 1 => 'Not Subscribed',
 ),
 ));//additional column

table1 has table1_id, salutation, f_name, l_name

table2 has table2_id, subscribed

First I added the new column and that displayed fine, then I added the join statement, but that returned an

Integrity constraint violation: 1052 Column 'table1_id' in on clause is ambiguous

So its obviously something to do with $collection->join() but what have I done wrong? I wasn't sure if it was due to the order I place the tables but reordering them failed, i'm obviously missing something

===EDIT===

$collection = Mage::getModel('module/model')->getCollection();
$collection->getSelect()->join( array('options'=>$this->getTable('module/table2')),'main_table.table1_id = options.table2_id','options.*');

Answer 1

When joining a table to a collection you'll have to use a prefix for the fields so every field is unique in the query.

$collection->getSelect()->join( array('options'=>'table2'),'`main_table`.`table1_id` = `options`.`table2_id`','`options`.*');

tbl2 is the Alias of table2 in this case which will give you a query like:

SELECT main_table.*, tbl2.unsubscribed FROM table1 AS main_table INNER JOIN table2 AS tbl2 ON main_table.table1_id = tbl2.table2_id

Print the query to check if prefixes are added on all the fields and to check for any other issues.

var_dump((string)$collection->getSelect());

You can check this Magento wiki page for a brief explanation on joining

• tony09uk: Well that makes sense. So I have var_dump() as you suggest and returned, string 'SELECT main_table.*, table2.unsubscribed FROM table1 AS main_table INNER JOIN table1 AS table2 ON table1_id = table2_id' (length=179) so I tried testdb_table1 and testdb_table2 also tried with a . between both threw an error, of can't retrieve entity config
• Sander Mangel: Sorry, i just noticed a mistake in the code, please check the update :)
• Sander Mangel: Awesome, I hope this will help you solve your join issues.
• tony09uk: Following the wiki article that you have shared all I can achieve is an empty sting for my query, any suggestions on what I may be doing wrong? I will add it as an EDIT in my original question
• Sander Mangel: I've updated my answer with your code and some updates in it, $this->getTable uses your extensions resource table to get the table name. I guess you don't have an extension there?