SQL different joins not making any difference to result

Question

I'm trying to write a quick (ha!) program to organise some of my financial information. 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 instances where there were no transactions for a month there will be no record. I get results like this:

---

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 on a table (well a View in this instance) which just contains a field Period 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'm actually getting though is the same result no matter how I join it (except CROSS JOIN which goes nuts, but it's really not what I wanted anyway, it was just to see if different joins are even doing anything). That is, there are no NULL records, it only returns the records that exist with corresponding periods in TableA instead of 12 records from 1 to 12 regardless. LEFT JOIN, RIGHT JOIN, INNER JOIN all fail to provide the NULL records I am expecting.

Is there something obvious that I'm doing wrong in the JOIN? Does it matter that I'm joining onto a View?

---

Edit Using 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's effectively getting the same result in a different way, still not getting the expected NULL entries for 3,5,6,7,etc.

---

Many thanks to Mark Byers for helping come to 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 a LedgerID field which is being grouped by, though the end result remains the same: filtering needs to be done on the JOIN, not on the result of the JOIN.

Answer 1

This is wrong:

WHERE Year='1997'

You want rows where Year is 1997 or is NULL but the NULLs are being filtered away because of that WHERE clause. Use this instead:

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

Note also that you don't need to persist the Periods table. You can also generate it dynamically using a recursive CTE. Your 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 what you need is LEFT OUTER JOIN

Answer 3

I think you are looking for a

FULL OUTER JOIN

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