Shaun Shaun - 11 months ago 40
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.

Answer Source

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