pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCTE or Subselect and outer joins not behaving as expected?
4+ messages / 3 participants
[nested] [flat]
* CTE or Subselect and outer joins not behaving as expected?
@ 2011-10-25 00:46 Joel Stevenson <jstevenson@bepress.com>
0 siblings, 2 replies; 4+ messages in thread
From: Joel Stevenson @ 2011-10-25 00:46 UTC (permalink / raw)
To: 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 );
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CTE or Subselect and outer joins not behaving as expected?
@ 2011-10-25 01:46 bricklen <bricklen@gmail.com>
parent: Joel Stevenson <jstevenson@bepress.com>
1 sibling, 0 replies; 4+ messages in thread
From: bricklen @ 2011-10-25 01:46 UTC (permalink / raw)
To: Joel Stevenson <jstevenson@bepress.com>; +Cc: pgsql-sql
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 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 );
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CTE or Subselect and outer joins not behaving as expected?
@ 2011-10-25 12:42 Harald Fuchs <hari.fuchs@gmail.com>
parent: Joel Stevenson <jstevenson@bepress.com>
1 sibling, 1 reply; 4+ messages in thread
From: Harald Fuchs @ 2011-10-25 12:42 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: CTE or Subselect and outer joins not behaving as expected?
@ 2011-10-26 17:22 Joel Stevenson <jstevenson@bepress.com>
parent: Harald Fuchs <hari.fuchs@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Joel Stevenson @ 2011-10-26 17:22 UTC (permalink / raw)
To: Harald Fuchs <hari.fuchs@gmail.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-26 17:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-25 00:46 CTE or Subselect and outer joins not behaving as expected? Joel Stevenson <jstevenson@bepress.com>
2011-10-25 01:46 ` bricklen <bricklen@gmail.com>
2011-10-25 12:42 ` Harald Fuchs <hari.fuchs@gmail.com>
2011-10-26 17:22 ` Joel Stevenson <jstevenson@bepress.com>
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