0

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...).

The 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
0

Solved by performaing a left join 'LEFT JOIN catalog_product_flat_1 cf ON cf.sku = si.sku' on the product sku rather then product ID since I was using the configurable products ID's I wouldn't get access to the MSRP field.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.