CODEWITHSUNDEEP
mysqlsqlmagento-1.9
IMD
IMD

Reputation: 23

why introducing date parameter messes up my query? 3 tables JOIN in mySQL

Writing a report that will return 3 columns

  1. list of all simple products
  2. current stock levels against each product
  3. sold qty in specified period against each product. If no sales, then show zero.
sku       | qty | sold qty    
product 1 | 5   | 15    
product 2 | 7   | 0    
product 3 | 0   | 15    
product 4 | 0   | 0

Code works as intended when not specifying a date period. However, that gives me entire sales history. I want to see sales history for, say, last month, quarter, year.

When specifying a period using above line it skips SKUs that had no sales in specified period.

sku       | qty | sold qty    
product 1 | 5   | 15    
product 3 | 0   | 15

I would like to see all SKUs, including those with zero sales.

SELECT 
    p.sku,
    FORMAT(s.qty, 0) AS qty,
    IFNULL(FORMAT(SUM(o.qty_invoiced), 0), 0) AS 'sold qty'
FROM
    mage_catalog_product_entity AS p
        LEFT JOIN
    mage_cataloginventory_stock_item AS s ON p.entity_id = s.product_id
        LEFT JOIN
    mage_sales_flat_order_item AS o ON p.entity_id = o.product_id
WHERE
    p.type_id = 'simple' 
       AND o.created_at BETWEEN '2018-11-01 00:00:01' AND '2019-01-31 23:59:59'
GROUP BY p.sku
ORDER BY SUM(o.qty_invoiced) DESC;

I would like to see all SKUs in column 1, including those with zero sales. It does that when looking at entire sales history and not specifying a date period. Not sure how adding a date period in the mix changes the result.

Upvotes: 2

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269653

The simplest method is to move the condition to the on clause:

SELECT p.sku,
       FORMAT(SUM(s.qty), 0) AS qty,
       COALESCE(FORMAT(SUM(o.qty_invoiced), 0), 0) AS `sold qty`
FROM mage_catalog_product_entity p LEFT JOIN
     mage_cataloginventory_stock_item s
     ON p.entity_id = s.product_id LEFT JOIN
     mage_sales_flat_order_item o
     ON p.entity_id = o.product_id AND
        o.created_at >= '2018-11-01' AND
        o.created_at < '2019-02-01'
WHERE p.type_id = 'simple' 
GROUP BY p.sku;

The key change is moving the condition on the date into the ON clause. That way, it doesn't turn the outer join into an inner join.

Other changes:

  • Note the change to the date arithmetic. Don't use between with dates. In this case, it is just messy and misses two seconds out of the day -- which I imagine is not intentional.
  • I much prefer COALESCE() instead of IFNULL() because COALESCE() is standard SQL.
  • Something needs to be done with s.qty. Either include it in the GROUP BY or use an aggregation function. I chose the latter, not knowing what the data looks like.

Upvotes: 0

Nick
Nick

Reputation: 147146

Your condition on o.created_at in your WHERE clause is converting the LEFT JOIN to mage_sales_flat_order_item into an INNER JOIN. To fix that, move

AND o.created_at BETWEEN '2018-11-01 00:00:01' AND '2019-01-31 23:59:59'

out of the WHERE clause and into the ON clause for the LEFT JOIN to mage_sales_flat_order_item

Upvotes: 1

Related Questions