Re: CTE or Subselect and outer joins not behaving as expected?

In article <A3B9626E-9093-4E7C-9986-BA6597665F3C@bepress.com>,
Joel Stevenson <jstevenson@bepress.com> writes:

> select tally_table. tally_mon, met.datum
> from (
>   select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date as tally_mon
>   from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as tally_day
> ) as tally_table full outer join my_existing_table as met on( tally_mon = met.month )
> where met.series = 1;
> -- ** See SETUP below **

> This produces rows only for those that exist in my_existing_table and no left join output which I'd expect for the
restof the tally_table rows.
 

Your WHERE clause turns the OUTER into an INNER JOIN.  Try

select tally_table.tally_mon, met.datum
from (  select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date as tally_mon  from generate_series(
0,( select current_date - '2011-06-01'::date ) ) as tally_day) as tally_table
 
left join my_existing_table as met on tally_mon = met.month and met.series = 1;