joining one table with itself

[Maarten via SQLMonster.com]

Hello all

Let's say I have 1 table "contract" containing the following data:

id year sales
45 2005 100
45 2004 95
89 2005 250
89 2004 275
12 2005 42

I want to make a table with one unique row for each id and then a column for
2004 sales and 2005 sales, like this:

select a.id, a.sales, b.sales
from contract a, contract b
where a.contract=b.contract(+)
and a.year=2005
and b.year=2004

The rows for id 45 and 89 are shown perfectly. But id 12 is not shown at all
because it doesn't have a record for 2004!! I don't know why 'cause I
outerjoined the tables.

It works perfectly when I have two distinct tables for each year (for
instance contract_2005 and contract_2004). So the problem seems to be in the
fact I like to join one table with itself.

Someone has a solution for this?

thanks!

Maarten

[Stu]

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

[Maarten via SQLMonster.com]

Hi Stu

It works, thanks a lot!

regards,
Maarten