Posted by Stu on 09/07/05 15:05

The problem is a logical one; by specifing that you want rows returned
from your result set where a.year =2005 and b.year=2004, you've limited
your return to rows that match BOTH criteria. Essentially, you've
eliminated the NULLS from your outer join.

To get around this, you need to use subqueries; I would also move to a
newer JOIN syntax (it's easier to read):

SELECT a.id, a.sales, b.sales
FROM (SELECT id, sales
FROM contract
WHERE year = 2005) a
LEFT JOIN (SELECT id, sales
FROM contract
WHERE year = 2004) b
ON a.id=b.id

Untested.

HTH,
Stu

[Back to original message]