Full outer join question.

[Paul McGarry <paul.mcgarry@xxxxxxxxx>]

Hi there everyone,

I'm having trouble getting the rows I want from a full outer join with
a where clause. Here is a simplified version of my tables:

======
create table lefty (
 day date,
 goodamount numeric(10,2),
 grp integer
);

insert into lefty values ('2005-06-01',5.00,1);
insert into lefty values ('2005-06-02',10.00,1);
insert into lefty values ('2005-06-01',2.50,2);


create table righty (
 day date,
 badamount numeric(10,2),
 grp integer
);

insert into righty values ('2005-06-01',-5.00,3);
insert into righty values ('2005-06-02',-10.00,1);
insert into righty values ('2005-06-02',-2.50,1);
======

The base query I want to do is:
SELECT grp, count(goodamount), sum(goodamount), count(badamount),
sum(badamount) FROM lefty FULL OUTER JOIN righty USING (grp) GROUP BY
grp;

This returns what I expect:
======
 grp | count |  sum  | count |  sum
-----+-------+-------+-------+--------
   3 |     0 |       |     1 |  -5.00
   2 |     1 |  2.50 |     0 |
   1 |     4 | 30.00 |     4 | -25.00
======

However, in the real world I don't want to do a query for the entire
tables, but for a particular date period, ie add restraints on
lefty.day and righty.day.

When I do that I lose all the rows whose grp isn't in both tables. For example:
======
SELECT grp, count(goodamount), sum(goodamount), count(badamount),
sum(badamount) FROM lefty FULL OUTER JOIN righty USING (grp)
WHERE lefty.day >= '2005-06-01' AND righty.day>='2005-06-01'
GROUP BY grp;
 grp | count |  sum  | count |  sum
-----+-------+-------+-------+--------
   1 |     4 | 30.00 |     4 | -25.00
======

Any ideas?

Paul

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly