LEFT INTERACTION WITH STATE IN THE THIRD TABLE

Question

SQL Fiddle Here

Brilliant SQL Writers,

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

Assuming the following tables:

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 | 

I am running SQL:

 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 

Expected Result:

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

However, as you can see in SQL Fiddle, 201401 and 201406 months are completely excluded from the results. I assume this is because he selected records with NextKey = 10, which were then filtered by IsValid = 0.

QUESTION: How can I get ALL MonthYearKeys items, even those that are currently filtered in my SQL?

Answer 1

Try the following:

 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 it here: http://www.sqlfiddle.com/#!3/4a333/26

EDIT

Without a subquery:

 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 a subquery, changing the JOIN priority:

 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

Answer 2

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

Use conditional aggregation instead:

 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;