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!
MaartenThe 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|||Hi Stu
It works, thanks a lot!
regards,
Maarten
No comments:
Post a Comment