From: Oliveiros <oliveiros.cristina@gmail.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: simple join is beating me
Date: Mon, 13 Jul 2009 12:45:49 +0100
Message-ID: <f54607780907130445k44c350a4teb60c03d77e38176@mail.gmail.com> (raw)
In-Reply-To: <200907131134.14335.gary.stainburn@ringways.co.uk>
References: <200907131134.14335.gary.stainburn@ringways.co.uk>
Howdy, Gary,
I have not the database in this computer, so I cannot test the sql I'm
sending you, but
if you do an outer join won't it result in what you need? Maybe I am not
reaching what you want to do...
SELECT deliveryQuery.o_date , orders, delivery
FROM (/* ur first query here */) ordersQuery
NATURAL RIGHT JOIN (/* ur second query goes here */) deliveryQuery
ORDER BY deliveryQuery.o_date DESC
Tararabite,
Oliveiros
@Allgarve
2009/7/13 Gary Stainburn <gary.stainburn@ringways.co.uk>
> hi folks
>
> i have the following:
>
> select o_ord_date as o_date, count(o_id) as orders
> from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE-'1
> month'::interval
> group by o_ord_date
> order by o_date desc
>
> and
>
> select o_act_del_date as o_date, count(o_id) as delivery
> from orders
> where o_de_id in (5,6) and
> o_act_del_date > CURRENT_DATE-'1 month'::interval and
> o_act_del_date <= CURRENT_DATE
> group by o_act_del_date
> order by o_date desc
>
> These give me
>
> o_date | orders
> ------------+--------
> 2009-07-10 | 4
> 2009-07-09 | 5
> 2009-07-08 | 12
> 2009-07-07 | 5
> 2009-07-06 | 2
> 2009-07-03 | 2
> 2009-07-02 | 7
> 2009-07-01 | 19
> 2009-06-30 | 20
> 2009-06-29 | 28
>
> and
>
> o_date | delivery
> ------------+----------
> 2009-07-13 | 5
> 2009-07-10 | 3
> 2009-07-09 | 4
> 2009-07-08 | 2
> 2009-07-07 | 4
> 2009-07-06 | 7
> 2009-07-03 | 6
> 2009-07-02 | 5
> 2009-07-01 | 3
> 2009-06-30 | 3
>
> How do i get
>
> o_date | orders | delivery
> ------------+--------+----------
> 2009-07-13 | | 5
> 2009-07-10 | 4 | 3
> 2009-07-09 | 5 | 4
> 2009-07-08 | 12 | 2
> 2009-07-07 | 5 | 4
> 2009-07-06 | 2 | 7
> 2009-07-03 | 2 | 6
> 2009-07-02 | 7 | 5
> 2009-07-01 | 19 | 3
> 2009-06-30 | 20 | 3
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
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: oliveiros.cristina@gmail.com, gary.stainburn@ringways.co.uk
Subject: Re: simple join is beating me
In-Reply-To: <f54607780907130445k44c350a4teb60c03d77e38176@mail.gmail.com>
* 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