From: | Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: CTE or Subselect and outer joins not behaving as expected? |
Date: | 2011-10-25 12:42:34 |
Message-ID: | 86zkgpi6o5.fsf@protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
In article <A3B9626E-9093-4E7C-9986-BA6597665F3C(at)bepress(dot)com>,
Joel Stevenson <jstevenson(at)bepress(dot)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;
From | Date | Subject | |
---|---|---|---|
Next Message | alan | 2011-10-25 14:12:17 | how to use explain analyze |
Previous Message | Craig Ringer | 2011-10-25 07:59:52 | Re: How to write sql to access another odbc source. |