I have these two tables
| date |
|---|
| 2017-1 |
| 2017-2 |
| 2017-3 |
| 2017-4 |
| 2017-5 |
| 2017-6 |
and
| date | customer | no_orders |
|---|---|---|
| 2017-1 | 156 | 1 |
| 2017-3 | 156 | 5 |
| 2017-5 | 156 | 4 |
| 2017-6 | 156 | 2 |
How can I join these two tables to have one row for each customer for all the dates same as below?
| date | customer | no_orders |
|---|---|---|
| 2017-1 | 156 | 1 |
| 2017-2 | 156 | 0 |
| 2017-3 | 156 | 5 |
| 2017-4 | 156 | 0 |
| 2017-5 | 156 | 4 |
| 2017-6 | 156 | 2 |
CodePudding user response：
We can use the following calendar table approach:
SELECT d.date, c.customer, COALESCE(t.no_orders, 0) AS no_orders
FROM dates d
CROSS JOIN (SELECT DISTINCT customer FROM customers) c
LEFT JOIN customers t
ON t.date = d.date AND
t.customer = c.customer
ORDER BY c.customer, d.date;
This assumes that the first table is called dates and the second table customers. The query works by using a cross join to generate a set of all dates and customers. We then left join to the second table to bring in the number of orders for a given customer on a given day. Absent number of orders are reported as zero.