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

On Mon, Oct 24, 2011 at 5:46 PM, Joel Stevenson <jstevenson@bepress.com> wrote:
> Hi all, I'm trying to enumerate a list of months between a date in the past and now and display a value from an
existingtable if there is one for the date or NULL if there isn't. 
>
> I'm using this SQL to generate the months:
>
> 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
>
> [[ produces ]]
>  tally_mon
> ------------
>  2011-06-01
>  2011-07-01
>  2011-08-01
>  2011-09-01
>  2011-10-01
> (5 rows)
>
> and I am trying to use it as the left hand side of a left join against my data table:
>
> 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. 
>
> What am I missing?
>
> Many thanks,
> Joel
>
> SETUP:
> create temp table my_existing_table ( month date not null, series int not null, datum int not null );
> insert into my_existing_table values ( '2011-08-01', 1, 5 ), ( '2011-10-01', 1, 4 );

UNION ALL should do it for you. Something along these lines should work

select tally_mon as mon, max(datum) as datum
from (
select distinct date_trunc( 'month', '2011-06-01'::date + tally_day
)::date as tally_mon,0 as datum from generate_series( 0, ( select
current_date - '2011-06-01'::date ) ) as tally_day
union all
select month as tally_mon,datum from my_existing_table
) as tally_table
group by mon
order by 1