I'm working on a query where I need to grab the MSRP from a simple product for orders placed to run some finance calculations (margin...).
sales_flat_order_item table includes the configurable product and the simple product with same skus, so it's same product but different data.
The configurable product in the table holds all the price values I need to generate a report, except for the custom attribute named cost which is only available through a join from the
catalog_product_flat_1 tables that is associated with the simple product.
I am currently filtering out the configurable products which means the order values wont be accessible for calculations.
Here is my current working query where
total_cost_sum is null because of null MSRP value:
SELECT so.entity_id AS 'Header ID', si.item_id AS 'Item ID', si.product_type, so.increment_id, si.sku , cf.msrp AS MSRP, cd.value AS 'Package Cost' , sum(round((cf.msrp + cd.value + 7 + 7 + 7), 2)) AS total_cost_sum , si.price AS 'Retail Price', si.price_incl_tax AS 'Retail Price Incl. Tax' , so.discount_amount AS 'Discount Amount', so.grand_total AS 'Total Revenue', so.base_subtotal_incl_tax FROM sales_flat_order AS so LEFT JOIN sales_flat_order_item AS si ON si.order_id = so.entity_id LEFT JOIN catalog_product_flat_1 cf ON cf.entity_id = si.product_id # To get access to MSRP JOIN catalog_product_entity_decimal cd ON cd.attribute_id = 236 # Get access to Package Cost. Custom Attribute > package_cost defaults to 5 WHERE so.store_id = 1 # Store 1 Orders Only AND si.product_type = 'configurable' # limit duplicates in calculation GROUP BY so.entity_id ORDER BY so.increment_id ASC, cf.msrp DESC