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
    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


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

1 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.


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

  • 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
    – tony09uk
    Sep 16, 2013 at 12:20
  • Sorry, i just noticed a mistake in the code, please check the update :) Sep 16, 2013 at 12:30
  • Awesome, I hope this will help you solve your join issues. Sep 16, 2013 at 12:52
  • 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
    – tony09uk
    Sep 16, 2013 at 14:15
  • 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? Sep 16, 2013 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.