LEFT OUTER JOIN With Condition In Third Table

Question

SQL Fiddle Here

Brilliant SQL Writers,

I am trying to get all dates in a certain range (stored as records in T1), and the sum of values of records in a related table (T2). However, some of the records in T2 will be filtered out by the value of a field in a third table (T3).

Assuming tables like this:

TABLE T1
| MonthYearKey |
|==============|
| 201401       |
| 201402       |
| 201403       |
| 201404       |
| 201405       |
| 201406       |

TABLE T2
| MonthYearKey | NextKey | MyValue |
|==============+=========+=========|
| 201402       | 6       | 10      |
| 201403       | 6       | 10      |
| 201404       | 6       | 10      |
| 201402       | 8       | 10      |
| 201403       | 8       | 10      |
| 201404       | 8       | 10      |
| 201401       | 10      | 10      |
| 201402       | 10      | 10      |
| 201406       | 10      | 10      |

TABLE T3
| NextKey | IsValid |
|=========+=========|
| 6       | 1       |
| 8       | 1       |
| 10      | 0       |

The SQL I am running is:

SELECT T1.MonthYearKey, SUM(ISNULL(T2.MyValue, 0)) AS SumOfValues
FROM T1
  LEFT OUTER JOIN T2 ON T1.MonthYearKey = T2.MonthYearKey
  LEFT OUTER JOIN T3 ON T2.NextKey = T3.NextKey
WHERE ISNULL(T3.IsValid, 1) = 1
GROUP BY T1.MonthYearKey

The output I expected is:

| MonthYearKey | SumOfValues |
|==============+=============|
| 201401       | 0           |
| 201402       | 20          |
| 201403       | 20          |
| 201404       | 20          |
| 201405       | 0           |
| 201406       | 0           |

However, as you can see in the SQL Fiddle, the 201401 and 201406 months are dropped out of the results altogether. I assume this is because it selected the records with NextKey = 10, which then was filtered out by the IsValid = 0.

QUESTION: How can I get ALL of the MonthYearKeys, even those that are currently being filtered out in my SQL?

Answer 1

When you apply the filter in the where clause, you lose the rows -- including the column used for group by.

Instead, use conditional aggregation:

SELECT T1.MonthYearKey,
       COALESCE(SUM(case when t3.isvalid is null or t3.isvalid = 1
                         then T2.MyValue
                    end), 0
               ) as SumOfValues
FROM T1 LEFT OUTER JOIN
     T2
     ON T1.MonthYearKey = T2.MonthYearKey LEFT OUTER JOIN T3
     ON T2.NextKey = T3.NextKey
GROUP BY T1.MonthYearKey;

Answer 2

Try this:

SELECT T1.MonthYearKey, SUM(ISNULL(T2.MyValue, 0)) AS SumOfValues
FROM T1
  LEFT OUTER JOIN (
    SELECT T2.MyValue, T2.MonthYearKey
    FROM T2
    JOIN T3 ON T2.NextKey = T3.NextKey AND ISNULL(T3.IsValid, 1) = 1
  ) T2 ON T1.MonthYearKey = T2.MonthYearKey
GROUP BY T1.MonthYearKey

Try here: http://www.sqlfiddle.com/#!3/4a333/26

EDIT:

Without nested query:

SELECT T1.MonthYearKey, SUM(ISNULL(T2.MyValue, 0)) AS SumOfValues
  FROM T2
  JOIN T3 ON T2.NextKey = T3.NextKey AND T3.IsValid = 1
  RIGHT OUTER JOIN T1 ON T1.MonthYearKey = T2.MonthYearKey
GROUP BY T1.MonthYearKey

Without nested query by changing JOIN precedence:

SELECT T1.MonthYearKey, SUM(ISNULL(T2.MyValue, 0)) AS SumOfValues
FROM T1
  LEFT OUTER JOIN (T2 JOIN T3 ON T2.NextKey = T3.NextKey AND T3.IsValid = 1)
  ON T1.MonthYearKey = T2.MonthYearKey
GROUP BY T1.MonthYearKey

SQL Fidle: http://www.sqlfiddle.com/#!3/4a333/45