How to set default value from mysql join interval yearmonth-CodePudding

I have problem with my query. I have two tables and I want join them to get the results based on primary key on first table, but I missing 1 data from first table.

this my fiddle

as you can see, I missing "xx3" from month 1

I have tried to change left and right join but, the results stil same. So as you can see I have to set coalesce(sum(b.sd_qty),0) as total, if no qty, set 0 as default.

CodePudding user response：

You should cross join the table to the distinct dates also:

SELECT a.item_code,
       COALESCE(SUM(b.sd_qty), 0) total,
       DATE_FORMAT(d.sd_date, '%m-%Y') month_year
FROM item a 
CROSS JOIN (
  SELECT DISTINCT sd_date 
  FROM sales_details 
  WHERE sd_date >= '2020-04-01' - INTERVAL 3 MONTH AND sd_date < '2020-05-01'
) d 
LEFT JOIN sales_details b 
ON a.item_code = b.item_code AND b.sd_date = d.sd_date
GROUP BY month_year, a.item_code
ORDER BY month_year, a.item_code;

Or, for MySql 8.0 , with a recursive CTE that returns the starting dates of all the months that you want the results, which can be cross joined to the table:

WITH RECURSIVE dates AS (
  SELECT '2020-04-01' - INTERVAL 3 MONTH AS sd_date
  UNION ALL
  SELECT sd_date   INTERVAL 1 MONTH
  FROM dates 
  WHERE sd_date   INTERVAL 1 MONTH < '2020-05-01'
)
SELECT a.item_code,
       COALESCE(SUM(b.sd_qty), 0) total,
       DATE_FORMAT(d.sd_date, '%m-%Y') month_year
FROM item a CROSS JOIN dates d 
LEFT JOIN sales_details b 
ON a.item_code = b.item_code AND DATE_FORMAT(b.sd_date, '%m-%Y') = DATE_FORMAT(d.sd_date, '%m-%Y')
GROUP BY month_year, a.item_code
ORDER BY month_year, a.item_code;

See the demo.