I have this line:

  'main_table.entity_id = sales_flat_order_address.parent_id', 
  array('telephone','city','postcode','country_id' ) 
->where("sales_flat_order_address.address_type =  'billing'");

The problem is that it works partially with joinLeft and "the other part" works with joinRight but if I use join it returns nothing(0 results).

And I'm showing the data like this:

$this->addColumn('telephone', array(
            'header' => Mage::helper('sales')->__('Telephone'),
            'index' => 'telephone',
            'filter_index' => 'sales_flat_order_address.telephone',

Magento version is 1.9.

The actual query:

  `sales_flat_invoice_grid` AS `main_table` 
  main_table.entity_id = sales_flat_order_address.parent_id 
  (sales_flat_order_address.address_type = 'billing')

In this case it returns the proper data but the "LEFT" field are empty, because I'm using RIGHT JOIN I guess.

  • Can you show use the full query and the left/right joins. Nov 9, 2014 at 19:55
  • 1
    Try doing echo $collection->getSelect()->__toString(); then run the same SQL in mysql client I find it easier to debug what I am doing wrong that way. Nov 9, 2014 at 20:49
  • I've updated my question with the actual query. Any help is appreciated!
    – Somebody
    Nov 9, 2014 at 20:57

1 Answer 1


In your query main_table.entity_id is the ID of an invoice whereas sales_flat_order_address.parent_id is the ID of an order. It doesn't make sense to join on those two columns.

Instead you need to use the order ID of each invoice:

// note this join clause!
    'main_table.order_id = sales_flat_order_address.parent_id',
    array('telephone', 'city', 'postcode', 'country_id')
->where("sales_flat_order_address.address_type = 'billing'");

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.