Ad

MySQL (SQL) | LEFT JOIN DOES NOT RETURN ALL ROWS (NO FILTER APPLIED)

- 1 answer

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

dateuser_idquantity
2020-01-03110
2020-01-12112
2020-01-2012
2020-01-01213
2020-01-2928

WISHED RESULT

dateuser_idquantity
2020-01-0110
2020-01-0210
2020-01-03110
2020-01-0410
2020-01-0510
2020-01-0610
2020-01-0710
2020-01-0810
2020-01-0910
2020-01-1010
2020-01-1110
2020-01-12112
2020-01-1310
2020-01-1410
2020-01-1510
2020-01-1610
2020-01-1710
2020-01-1810
2020-01-1910
2020-01-2012
2020-01-2110
2020-01-2110
2020-01-2210
2020-01-2310
2020-01-2410
2020-01-2510
2020-01-2610
2020-01-2710
2020-01-2810
2020-01-2910
2020-01-3010
2020-01-3110
2020-01-01213
2020-01-0220
2020-01-0320
2020-01-0420
2020-01-0520
2020-01-0620
2020-01-0720
2020-01-0820
2020-01-0920
2020-01-1020
2020-01-1120
2020-01-1220
2020-01-1320
2020-01-1420
2020-01-1520
2020-01-1620
2020-01-1720
2020-01-1820
2020-01-1920
2020-01-2020
2020-01-2120
2020-01-2120
2020-01-2220
2020-01-2320
2020-01-2420
2020-01-2520
2020-01-2620
2020-01-2720
2020-01-2820
2020-01-2928
2020-01-3020
2020-01-3120
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
Ad