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:
SELECT * 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.