How to do multiple join query in magento

Question

I have to add Extra field like buyer name in Admin sales order create section

Mage_Adminhtml_Block_Sales_Order_Create_Search_Grid

In collection Add code like

 $collection->getSelect()->join( 
        't2'=>'table2_to_be_joined',
        'main_table.table_field_id ='t2.field_id', array('field_name'));

New products field are coming excellently.

now we need to add one more table

field to check table_2 field_name == table_3 field_name must equal.

After adding this join query result

$collection->getSelect()->join( array(
      't2'=>'table2_to_be_joined', 't3' => 'table3_to_be_joind'),
        'main_table.table_field_id ='t2.field_id' and 't2.field_name = t3.field_name',array('field_name'));

Its showing

Unknown column 't3.field_name' in 'on clause', query

Answer 1

Try

 $collection->getSelect()->join( 
    array('t2'=>'table2_to_be_joined'),
    'main_table.table_field_id = t2.field_id',
     array('t2.field_name')
  );

  $collection->getSelect()->join( 
    array('t3'=>'table3_to_be_joind'),
    't2.field_name = t3.field_name'
    array('t3.field_name')
  );