SQL different joins have no meaning for the result

Question

I am trying to write a short (ha!) Program to organize some of my financial data. What I ideally want is a query that will return all records with financial information in them from TableA. There should be one row for each month, but in cases where there were no transactions during the month, there will be no record. I get the following results:

---

SELECT Period,Year,TotalValue FROM TableA WHERE Year='1997' 

Result:

 Period Year TotalValue 1 1997 298.16 2 1997 435.25 4 1997 338.37 8 1997 336.07 9 1997 578.97 11 1997 361.23 

---

By joining a table (well, the view in this instance), which simply contains a Period field with values ​​from 1 to 12, I expect to get something like this:

---

 SELECT p.Period,a.Year,a.TotalValue FROM Periods AS p LEFT JOIN TableA AS a ON p.Period = a.Period WHERE Year='1997' 

Result:

 Period Year TotalValue 1 1997 298.16 2 1997 435.25 3 NULL NULL 4 1997 338.37 5 NULL NULL 6 NULL NULL 7 NULL NULL 8 1997 336.07 9 1997 578.97 10 NULL NULL 11 1997 361.23 12 NULL NULL 

---

What I actually get is the same result, no matter how I join it (except for CROSS JOIN, which is crazy, but it's really not what I wanted, it's just to see, That yes, there are no NULL records, it returns only records that exist with the corresponding periods in table A, instead of 12 records from 1 to 12. LEFT JOIN, RIGHT JOIN, INNER JOIN cannot provide the NULL records that I expect.

Is there something obvious that I'm wrong about JOIN? Does it matter that I join the view?

---

Edit Using the Mark Byers example, I tried the following:

 SELECT p.Period,a.Year,a.TotalValue FROM Periods AS p LEFT JOIN TableA AS a ON (p.Period = a.Period) AND (a.Year = '1997') 

Result:

 Period Year TotalValue 1 1997 298.16 2 1997 435.25 4 1997 338.37 8 1997 336.07 9 1997 578.97 11 1997 361.23 

It effectively gets the same result differently, still not getting the expected NULL entries for 3,5,6,7, etc.

---

Many thanks to Mark Byers for helping with the final solution, which for the record was:

 SELECT p.Period, a.YEAR, SUM(a.Value) as TotalValue FROM Periods as p LEFT JOIN TableA as a ON d.Period = p.Period AND a.Year = '1997' GROUP BY p.Period,a.Year,a.PERIOD ORDER BY p.Period,a.Year; 

In practice, there is also the LedgerID field, which is grouped, although the end result remains the same: filtering should be performed in the JOIN, and not as a result of the JOIN.

Answer 1

It is not right:

 WHERE Year='1997' 

You need strings in which Year 1997 or NULL, but NULL are filtered out due to this WHERE clause. Use this instead:

 LEFT JOIN TableA AS a ON p.Period = a.Period AND Year = '1997' 

Also note that you do not need to save the Periods table. You can also generate it dynamically using a recursive CTE . A recursive CTE should look like this:

 WITH Periods (Period) AS ( SELECT 1 UNION ALL SELECT Period + 1 FROM Periods WHERE Period < 12 ) SELECT * FROM Periods 

Answer 2

I think you need a LEFT OUTER JOIN

Answer 3

I think you are looking

 FULL OUTER JOIN 

because for every NULL "Year" its corresponding "TotalValue" is also NULL!