I am writing a sales by product by day (based on invoice create date) report.

I am getting two result rows in a custom report for a configurable product with formula choice attribute i.e. 1 pound of peppers, red (red contains the formula choice and is a markup in price $2 over the base price of $5 for 1 pound of peppers).

The product id for 1 pound of peppers is 123 and the product id for 1 pound peppers, red is 124 (labeled as 'Not visible individually' because it is a configurable product).

When querying the sales_flat_invoice_item table both rows are returned, looking like two products, even though they are the same product, one is just configured, as such:

Product Name            product_id          price
1 pound peppers           123                $7
1 pound peppers, red      124                NULL

but I only want one row returned, a combination of the two above, to be:

Product Name            product_id            price
1 pound peppers, red     whatever              $7

My sql for the report can be stripped down to:

  FROM sales_flat_invoice A
  INNER JOIN sales_flat_invoice_item B
   ON A.entity_id= B.parent_id
  WHERE DATE_FORMAT(A.created_at,'%Y-%m-%d') = '2015-01-19'

so long as the only product sold on that day is the configurable pepper product (then you will see both rows in sales_flat_invoice_item).

How can I get only a combined, single row in the output as I mentioned above? One problem is the price is on the generic item but the product name, specifically including "red" (for inventory purposes) is on the specific item and not the generic.

Your Answer

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

Browse other questions tagged or ask your own question.