php - Magento how to join the table with resultset in query -

i new magento. used magento 1.9 , want join 1 table , sub-query. want join sales_flat_order sub-query of table "sales_flat_order_item".

the sql follows:

select `o`.`store_id`, `o`.`status` `order_status`,      count(o.entity_id)  `orders_count`,      sum(oi.total_qty_ordered) `total_qty_ordered` `sales_flat_order` `o` inner join     (select `sales_flat_order_item`.`order_id`,          sum(qty_ordered -  ifnull(qty_canceled, 0)) `total_qty_ordered`      `sales_flat_order_item`     (parent_item_id null)      group `order_id`) `oi`  on oi.order_id = o.entity_id  (o.state not in ('pending_payment', 'new'))  group `o`.`store_id`, `o`.`status` 

i have knew how join 2 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 join 2 tables, want know how join table "sales_flat_order" sub-query of table "sales_flat_order_item".

could me?

thanks