Magento how to join the table with resultset in query

227
April 06, 2017, at 8:33 PM

I am new to magento. I used magento 1.9 and I just want to join one table and sub-query. I want to join sales_flat_order with sub-query of table "sales_flat_order_item".

The sql is as follows:

SELECT `o`.`store_id`, `o`.`status` AS `order_status`, 
    COUNT(o.entity_id) AS  `orders_count`, 
    SUM(oi.total_qty_ordered) AS `total_qty_ordered`
FROM `sales_flat_order` AS `o`
INNER JOIN 
   (SELECT `sales_flat_order_item`.`order_id`, 
        SUM(qty_ordered -  IFNULL(qty_canceled, 0)) AS `total_qty_ordered` 
    FROM `sales_flat_order_item`
    WHERE (parent_item_id IS NULL) 
    GROUP BY `order_id`) AS `oi` 
ON oi.order_id = o.entity_id 
WHERE (o.state NOT IN ('pending_payment', 'new')) 
GROUP `o`.`store_id`, `o`.`status`

I have knew how to join two tables.

$collection = Mage::getResourceModel($this->_getCollectionClass());
$collection->join(array('oi'=> 'sales/order_item'),'oi.order_id = o.entity_id', array(...));
$this->setCollection($collection);`

But don't want to join two tables, I want to know how to join table "sales_flat_order" with the sub-query of table "sales_flat_order_item".

Could someone help me?

Thanks

READ ALSO
PHP function not returning TRUE [duplicate]

PHP function not returning TRUE [duplicate]

This question already has an answer here:

268
php can not execute after selected mysql db [duplicate]

php can not execute after selected mysql db [duplicate]

This question already has an answer here:

247
Browser showing � instead of Š (as stored in MySQL) [duplicate]

Browser showing � instead of Š (as stored in MySQL) [duplicate]

This question already has an answer here:

205
MySQL - join in one query

MySQL - join in one query

I have 2 tables:

163