Обсуждение: CTE or Subselect and outer joins not behaving as expected?
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-012011-07-012011-08-012011-09-012011-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 ofthe 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 );
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
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;
Ah, yes! Thanks for the help. On Oct 25, 2011, at 5:42 AM, Harald Fuchs wrote: > Joel Stevenson <jstevenson@bepress.com> writes: > > 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;