I have this line:
$collection->getSelect()->join( 'sales_flat_order_address', '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:
SELECT `main_table`.*, `sales_flat_order_address`.`telephone`, `sales_flat_order_address`.`city`, `sales_flat_order_address`.`postcode` FROM `sales_flat_invoice_grid` AS `main_table` RIGHT JOIN `sales_flat_order_address` ON main_table.entity_id = sales_flat_order_address.parent_id WHERE (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.