Posted by Maarten via SQLMonster.com on 09/07/05 14:57

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

• Next in forum: joining one table with itself
• Prev in forum: joining one table with itself
• Thread view: joining one table with itself

[Reply to this message]