Same table query

[mmouse]

Greetings All,

I am trying to do a week on week query. The basic idea is we see how much extra (or less) we have sold compared to last week.


Query:
SELECT TblSub.SubName, TblWeekly.LineId, TblLine.LineDesc, TblWeekly.Unit, TblWeekly_1.Unit AS Expr1, TblWeekly.Reg, TblWeekly_1.Reg AS Expr2,
TblWeekly.Was, TblWeekly_1.Was AS Expr3, TblWeekly.Red, TblWeekly_1.Red AS Expr4, TblWeekly.Con2, TblWeekly_1.Con2 AS Expr5
FROM TblWeekly TblWeekly_1 RIGHT OUTER JOIN
TblWeekly INNER JOIN
TblLine ON TblWeekly.LineId = TblLine.LineId INNER JOIN
TblCoy ON TblLine.LineCoy = TblCoy.ID INNER JOIN
TblCat ON TblLine.LineCat = TblCat.ID INNER JOIN
TblSub ON TblLine.LineSubGroup = TblSub.Id ON TblWeekly_1.LineId = TblWeekly.LineId AND TblWeekly_1.WEndID = TblWeekly.WEndID - 7
WHERE (TblCat.CatName = 'Company') AND (TblWeekly.WEndID = CONVERT(DATETIME, '13/01/2007', 103))
ORDER BY TblSub.SubName DESC, TblWeekly.LineId

The results are (trimmed)
TblLine.LineDesc, TblWeekly.Unit, TblWeekly_1.Unit
12345 100 90
54321 90 1000
98765 100 <null>
56789 <null> 1000 <-this line does not show in the query

Product 56789 does not show in the query because there are no sales for the previous week.

It must be a 'join' issue somewhere, but I can;t see it.

Many thanks for having a look

[mmouse]

Sorted it out.