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.

P粉287726308 · Answer

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 
 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 
ViewDemo.