I would like to left join a calendar table with a sales table and return all sales by user by day (inclusive of all days without sales). For some reason, however, I only get the days with sales. As you can see below, there are no filters applied. What am I doing wrong here?
Below my SQL code, all tables and the wished final result.
MySQL Query
SELECT
calendar.date AS date,
sales.user_id AS user_id,
CASE
WHEN sales.date = calendar.date THEN sales.quantity
ELSE 0
END AS quantity
FROM calendar
LEFT JOIN sales
ON calendar.date = sales.date
DATE table (from 2020-01-01 to 2020-01-31)
| date |
|---|
| 2020-01-01 |
| 2020-01-02 |
| ... |
| 2020-01-31 |
SALES table
| date | user_id | quantity |
|---|---|---|
| 2020-01-03 | 1 | 10 |
| 2020-01-12 | 1 | 12 |
| 2020-01-20 | 1 | 2 |
| 2020-01-01 | 2 | 13 |
| 2020-01-29 | 2 | 8 |
WISHED RESULT
| date | user_id | quantity |
|---|---|---|
| 2020-01-01 | 1 | 0 |
| 2020-01-02 | 1 | 0 |
| 2020-01-03 | 1 | 10 |
| 2020-01-04 | 1 | 0 |
| 2020-01-05 | 1 | 0 |
| 2020-01-06 | 1 | 0 |
| 2020-01-07 | 1 | 0 |
| 2020-01-08 | 1 | 0 |
| 2020-01-09 | 1 | 0 |
| 2020-01-10 | 1 | 0 |
| 2020-01-11 | 1 | 0 |
| 2020-01-12 | 1 | 12 |
| 2020-01-13 | 1 | 0 |
| 2020-01-14 | 1 | 0 |
| 2020-01-15 | 1 | 0 |
| 2020-01-16 | 1 | 0 |
| 2020-01-17 | 1 | 0 |
| 2020-01-18 | 1 | 0 |
| 2020-01-19 | 1 | 0 |
| 2020-01-20 | 1 | 2 |
| 2020-01-21 | 1 | 0 |
| 2020-01-21 | 1 | 0 |
| 2020-01-22 | 1 | 0 |
| 2020-01-23 | 1 | 0 |
| 2020-01-24 | 1 | 0 |
| 2020-01-25 | 1 | 0 |
| 2020-01-26 | 1 | 0 |
| 2020-01-27 | 1 | 0 |
| 2020-01-28 | 1 | 0 |
| 2020-01-29 | 1 | 0 |
| 2020-01-30 | 1 | 0 |
| 2020-01-31 | 1 | 0 |
| 2020-01-01 | 2 | 13 |
| 2020-01-02 | 2 | 0 |
| 2020-01-03 | 2 | 0 |
| 2020-01-04 | 2 | 0 |
| 2020-01-05 | 2 | 0 |
| 2020-01-06 | 2 | 0 |
| 2020-01-07 | 2 | 0 |
| 2020-01-08 | 2 | 0 |
| 2020-01-09 | 2 | 0 |
| 2020-01-10 | 2 | 0 |
| 2020-01-11 | 2 | 0 |
| 2020-01-12 | 2 | 0 |
| 2020-01-13 | 2 | 0 |
| 2020-01-14 | 2 | 0 |
| 2020-01-15 | 2 | 0 |
| 2020-01-16 | 2 | 0 |
| 2020-01-17 | 2 | 0 |
| 2020-01-18 | 2 | 0 |
| 2020-01-19 | 2 | 0 |
| 2020-01-20 | 2 | 0 |
| 2020-01-21 | 2 | 0 |
| 2020-01-21 | 2 | 0 |
| 2020-01-22 | 2 | 0 |
| 2020-01-23 | 2 | 0 |
| 2020-01-24 | 2 | 0 |
| 2020-01-25 | 2 | 0 |
| 2020-01-26 | 2 | 0 |
| 2020-01-27 | 2 | 0 |
| 2020-01-28 | 2 | 0 |
| 2020-01-29 | 2 | 8 |
| 2020-01-30 | 2 | 0 |
| 2020-01-31 | 2 | 0 |
CodePudding user response：
The user is also unknown for the day without sales. To solve this, you can do something like this:
SELECT
calendar.date AS date,
su.user_id AS user_id,
COALESCE( sales.quantity,0) AS quantity
FROM calendar
CROSS JOIN (SELECT DISTINCT user_id FROM sales) su
LEFT JOIN sales
ON calendar.date = sales.date AND sales.user_id=su.user_id
ORDER BY 2,1
see fiddle