Ad
MySQL (SQL) | LEFT JOIN DOES NOT RETURN ALL ROWS (NO FILTER APPLIED)
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 |
Ad
Answer
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
Ad
source: stackoverflow.com
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key
Ad