Change this query so it returns NULL rows when the value doesn't exist, otherwise return the value

SELECT
    WCC.WardCensusTypesId, M.MonthsId, WCT.WardCensusTypesName,
    Y.YearsId, WCC.WardCensusCountsNum
FROM WardCensusTypes WCT
LEFT JOIN WardCensusCounts WCC ON 1=1
LEFT JOIN Months M ON 1=1
LEFT JOIN Years Y On Y.YearsId=3

WardCensusTypes contains all the types (6 of them)

Months contains just a list of months (1-12)

Years contains formatted years (yearsid = 1 : '2010-2011', yearsid=2:'2011-2012', and so on)

WardCensusCounts contains a WardCensusCountsNum for each, given the monthid, YearsId, and WardCensusTypesId.

WardCensusCounts contains 1 record: WardCensusTypesId=1, MonthsId=1, YearsId=3, WardCensusCountsNum=5

When I do the开发者_Python百科 big query above I get a '10' for WCC.WardCensusCountsNum for each row, the query returns 72 rows, all with the value of 10 for the WCT.WardCensusCountsNum field. What I want is the value 10 for the 1 row that matches MonthsId, YearsId, and WardCensusTypesId, but all the other rows (71 of them) should return NULL for that field. Is there a way to change the query to do this?

I think you need a LEFT JOIN only to table WardCensusCounts. Since you want all combinations of the other 3 tables, CROSS JOIN of those 3 tables is more probably what you need:

SELECT
    WCT.WardCensusTypesId            --- chnaged this to WCT. (not WCC.)
  , M.MonthsId
  , WCT.WardCensusTypesName 
  , Y.YearsId
  , WCC.WardCensusCountsNum
FROM
    WardCensusTypes WCT
  CROSS JOIN
    Months M 
  CROSS JOIN
    Years Y
  LEFT JOIN
    WardCensusCounts WCC
      ON  WCC.WardCensusTypesId = WCT.WardCensusTypesId
      AND WCC.MonthsId = M.MonthsId 
      AND WCC.YearsId = Y.YearsId
WHERE Y.YearsId = 3

Regarding CROSS JOIN:

SELECT *
FROM 
    a
  CROSS JOIN
    b

can also be coded (perhaps for older dbms that do not have CROSS JOIN) as:

SELECT *
FROM 
    a
  , b

or like what you had (since 1=1 is always true, there is no difference if you have JOIN or LEFT JOIN):

SELECT *
FROM 
    a
  JOIN
    b
  ON 1=1