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). 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?
