Chained join not filtering as expected

Question

I have a related question here:

Generate month data series with null months included?

When I posted I omitted the second join thinking it was trivial and not impact my question - I was wrong there.

SELECT TO_CHAR(activity_tasks.start_date, 'MON') AS month,
EXTRACT(MONTH FROM activity_tasks.start_date) AS month_num,
SUM(cost_planned) FILTER (WHERE activity_tasks.aasm_state IN ('open', 'planned' ) )  AS planned,
SUM(cost_actual) FILTER (WHERE activity_tasks.aasm_state IN ('closed' ) ) AS actual
FROM "activity_tasks"
INNER JOIN "activities"."id" = "activity_tasks"."activity_id"
WHERE "activities"."type" = 'My Type' AND activity_tasks.start_date >= '2020-01-01' AND activity_tasks.start_date <= '2020-12-31'
GROUP BY month, month_num
ORDER BY month_num

Which gets me:

+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| NOV   | 11        | NULL    | 123    |
| DEC   | 12        | 500     | NULL   |
+-------+-----------+---------+--------+

The accepted answer on the related had me try various modifications like adding:

LEFT JOIN activities a
ON a.id = t.activity_id AND a.type = 'My Type'

which results in (not filtering out just My Type):

+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| JAN   | 1         | NULL    | NULL   |
| FEB   | 2         | NULL    | NULL   |
| MAR   | 3         | NULL    | NULL   |
| APR   | 4         | NULL    | NULL   |
| MAY   | 5         | NULL    | NULL   |
| JUN   | 6         | NULL    | NULL   |
| JUL   | 7         | NULL    | NULL   |
| AUG   | 8         | NULL    | NULL   |
| SEP   | 9         | NULL    | NULL   |
| OCT   | 10        | 0       | 0      |
| NOV   | 11        | 125     | 123    |
| DEC   | 12        | 1414    | 0      |
+-------+-----------+---------+--------+

Tried this:

ON a.id = t.activity_id
WHERE a.type = 'My Type'

Which gets me the correct filtered results but I lose the filtered results again:

+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| NOV   | 11        | NULL    | 123    |
| DEC   | 12        | 500     | NULL   |
+-------+-----------+---------+--------+

I am looking for this - the filtered tasks with the 'null' months also:

+-------+-----------+---------+--------+
| month | month_num | planned | actual |
+-------+-----------+---------+--------+
| JAN   | 1         | NULL    | NULL   |
| FEB   | 2         | NULL    | NULL   |
| MAR   | 3         | NULL    | NULL   |
| APR   | 4         | NULL    | NULL   |
| MAY   | 5         | NULL    | NULL   |
| JUN   | 6         | NULL    | NULL   |
| JUL   | 7         | NULL    | NULL   |
| AUG   | 8         | NULL    | NULL   |
| SEP   | 9         | NULL    | NULL   |
| OCT   | 10        | NULL    | NULL   |
| NOV   | 11        | NULL    | 123    |
| DEC   | 12        | 500     | NULL   |
+-------+-----------+---------+--------+

I have tried all types of JOINS for the second join. Not sure what I am missing here.

Sorry - I normally re-read to ensure I have clearly asked an specific question. — Dan Tappin, Dec 15 '20 at 21:12

score 0 · Answer 1 · answered Dec 15 '20 at 23:51

Realized I need a sub-select like this:

SELECT 
  UPPER(TO_CHAR(d.start_date, 'mon')) AS month,
  EXTRACT(MONTH FROM d.start_date) AS month_num,
  SUM(t.cost_planned) FILTER (WHERE t.aasm_state IN ('open', 'planned' ) ) AS planned,
  SUM(t.cost_actual)  FILTER (WHERE t.aasm_state = 'closed') AS actual
FROM GENERATE_SERIES('2020-01-01'::date, '2020-12-01'::date, '1 month') d(start_date)
LEFT JOIN
(
SELECT t.*
FROM activity_tasks t
LEFT JOIN activities a
ON a.id = t.activity_id
WHERE a.type = 'My Type'
) AS t ON t.start_date >= d.start_date and t.start_date < d.start_date + '1 month'::interval
GROUP BY d.start_date
ORDER BY d.start_date

Chained join not filtering as expected

1 Answers1

Linked