From: | Joel Stevenson <jstevenson(at)bepress(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | CTE or Subselect and outer joins not behaving as expected? |
Date: | 2011-10-25 00:46:42 |
Message-ID: | A3B9626E-9093-4E7C-9986-BA6597665F3C@bepress.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
------------
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 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 );
From | Date | Subject | |
---|---|---|---|
Next Message | bricklen | 2011-10-25 01:46:46 | Re: CTE or Subselect and outer joins not behaving as expected? |
Previous Message | Guillaume Lelarge | 2011-10-24 21:24:12 | Re: new user on mac |