sql help req

[subs]

SELECT [Current Year].GL, [Current Year].OZIP, [Current Year].Dzip,
[Current Year].SumOfACTUAL_WGT, [Current Year].SumOfPAID_AMT, [Current
Year].CountOfship_DATE, [Current Year-1].SumOfACTUAL_WGT, [Current
Year-1].SumOfPAID_AMT, [Current Year-1].CountOfship_DATE

FROM [Current Year] LEFT JOIN [Current Year] AS [Current Year-1] ON
([Current Year].theyear=[Current Year-1].theyear-1) AND ([Current
Year].DZIP=[current year-1].Dzip) AND ([Current Year].OZIP=[Current
Year-1].OZIP) AND ([Current Year].GL=[Current Year-1].GL)

WHERE ([Current Year].theyear=[Current Year-1].theyear-1);


i am trying to compare the totals of paid amt, wgt etc for two
consecutive years in the above query. But the query is returning only
those rows where there is data in both the years.

But i need a query which can compare the totals for two consecutive
years irrespective of whether there is data in both the years. Should
i use a different type of Join? i am giving another example- original
table t

ozip dzip sum of wgt sum of paid amt year
a b 2000 2333 2009
a b 33 444 2008
c d 44 3333 2009
x y 33 33333 2009
x y 222 2222 2008


output required - Pls note i require the second row also

ozip dzip sum of wgt in 2009 sum of wgt in 2008 sum of paid
amt 2009 sum of paid amt 2008
a b 2000
33 2333
444
c d 44 3333
x y 33
222 33333 2222

what should be sql query - pls help thanks

 

[Sylvain Lafontaine]

You have correctly chosen to use a Left Join; however, by adding the filter
([Current Year].theyear=[Current Year-1].theyear-1) to the WHERE clause, you
are effectively transforming your Left Join into a regular Inner Join.
Remove it and your query should be OK.