Magento 1: How to join these tables to get the result

Question

$collection     = $this->_prepareEntityCollection(Mage::getResourceModel('customer/customer_collection'));

    foreach($collection as $customer){
       $_orders = Mage::getModel('sales/order')->getCollection()->addFieldToFilter('customer_id',$customer->getId());
       foreach($_orders as $_ordersitem){

                $orderid    =   $_ordersitem->getEntityId();
                $collection_sales = Mage::getModel('sales/order_item')->load($orderid);
                 $pro_id  =   $collection_sales->getProductId();
                $_product = Mage::getModel('catalog/product')->load($pro_id);
                 echo '<pre>';print_r($_product->getCategoryIds());
        }
    }

Answer 1

Try the below query,

SELECT sales_order_item.order_id, sales_order_item.product_id, sales_order.customer_id, catalog_category_product.category_id, catalog_category_entity_varchar.value FROM sales_order_item INNER JOIN sales_order ON sales_order_item.order_id = sales_order.entity_id INNER JOIN catalog_category_product ON sales_order_item.product_id = catalog_category_product.product_id INNER JOIN catalog_category_entity_varchar ON catalog_category_product.category_id = catalog_category_entity_varchar.entity_id GROUP BY catalog_category_product.category_id, sales_order.customer_id