I am creating some custom reporting in a Magento module, and I am struggling with the SQL on this one. I have put the SQL below, and basically, I want this to return a row for each row in the 'donor' table. It is basically getting the total value of sales of products that have a certain attribute.
SELECT b.entity_id AS 'Car ID', b.car_name AS 'Car Name', b.car_purchase_price AS 'Car Purchased For', SUM(s.price) AS 'Total Sales'
FROM sales_flat_order_item AS s
INNER JOIN catalog_product_entity_int AS c ON s.sku = c.entity_id
INNER JOIN bs_donor AS b ON c.value = b.entity_id
WHERE c.attribute_id = '971'
AND c.value = '50'
You can reverse the order in which you do the joins, such that the
bs_donor table appears first. Then use
LEFT JOIN instead of
INNER JOIN to ensure that every record from
bs_donor appears in the result set.
SELECT b.entity_id AS 'Car ID', SUM(s.price) AS 'Total Sales' FROM bs_donor b LEFT JOIN catalog_product_entity_int c ON c.value = b.entity_id LEFT JOIN sales_flat_order_item s ON s.sku = c.entity_id WHERE c.attribute_id = '971' GROUP BY b.entity_id