From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: simple join is beating me
Date: Mon, 13 Jul 2009 14:12:04 +0100
Message-ID: <200907131412.04729.gary.stainburn@ringways.co.uk> (raw)
In-Reply-To: <f54607780907130445k44c350a4teb60c03d77e38176@mail.gmail.com>
References: <200907131134.14335.gary.stainburn@ringways.co.uk>
<f54607780907130445k44c350a4teb60c03d77e38176@mail.gmail.com>
Hi Oliveiros,
Thank you for this. However, this does not give me what I want.
If a date exists where we have orders but no deliveries the row does not
appear.
I have tried doing a union to link the two selects together, but i still
cannot get anything to work.
Gary
On Monday 13 July 2009 12:45:49 Oliveiros wrote:
> 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
--
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
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: gary.stainburn@ringways.co.uk
Subject: Re: simple join is beating me
In-Reply-To: <200907131412.04729.gary.stainburn@ringways.co.uk>
* 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