Shaun Shaun - 5 months ago 19
MySQL Question

MySQL Query 3 Table Inner Joins Only Returning 1 Row

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'

This works, but return only 1 row, even thought there are around 20 in the bs_donor table, and it also returns, what looks like, a total of sales for all products, not just the ones that have that particular attribute set. If I add this to the end of the query:

AND c.value = '50'

It gets the correct data, and is exactly what I need, but obviously just for the bs_donor row that has an ID of '50' or whatever I set there. I am assuming there is an error in the inner joins, and have tried using nested sub-query's, but just get lost.

I think it may be because I am getting data FROM sales_flat_order_item, instead of bs_donor, but I just can't get it running any other way.

Any help would be much appreciated.


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