From: Joel Stevenson <jstevenson@bepress.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Date: Mon, 24 Oct 2011 17:46:42 -0700
Subject: CTE or Subselect and outer joins not behaving as expected?
To: pgsql-sql@postgresql.org
Message-Id: <A3B9626E-9093-4E7C-9986-BA6597665F3C@bepress.com>
Mime-Version: 1.0 (Apple Message framework v1244.3)

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 existing table 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 =20
------------
 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 =
=3D met.month )
where met.series =3D 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 rest of 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 );