Set the default value for MySQL connection interval year and month

Question

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

This is my violin

As you can see, I'm missing "xx3" for month 1

I tried changing the left and right connections but still the same result. So as you can see, I have to set coalesce(sum(b.sd_qty),0) to the total, if there is no qty, then 0 is the default value.

Answer 1

You should also cross-join the table to different dates:

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;

Alternatively, for MySql 8.0, use a recursive CTE to return the start dates of all months for which you want 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;

ViewDemo.