From: Harald Fuchs <hari.fuchs@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: CTE or Subselect and outer joins not behaving as expected?
Date: Tue, 25 Oct 2011 14:42:34 +0200
Message-ID: <86zkgpi6o5.fsf@protecting.net> (raw)
References: <A3B9626E-9093-4E7C-9986-BA6597665F3C@bepress.com>
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 rest of 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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hari.fuchs@gmail.com
Subject: Re: CTE or Subselect and outer joins not behaving as expected?
In-Reply-To: <86zkgpi6o5.fsf@protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox