Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id ACF3F633BF6 for ; Mon, 13 Jul 2009 08:46:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 98110-02 for ; Mon, 13 Jul 2009 08:45:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f205.google.com (mail-fx0-f205.google.com [209.85.220.205]) by mail.postgresql.org (Postfix) with ESMTP id B333A633B09 for ; Mon, 13 Jul 2009 08:45:51 -0300 (ADT) Received: by fxm1 with SMTP id 1so2051995fxm.19 for ; Mon, 13 Jul 2009 04:45:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=ayy5F5+4p0hwhTNHp9WjCtxiASgI+A9LYUd+FppwvQ4=; b=KCM5pRqKKlzDmvaxB4GY1rVgNkFAw4bxoYnOIwVMU2dTrbNTK1JYF5+fblohkmFy2E 26VwB7RhmkBibDtHJyw/oZ/Vi7iA9ARUpnYgPA6kpP6a9kecLfQ+FFVa9T3ycMMgh6wx oQxGMSEgvmimUduAFK/aOGrh0qyH1nIFF8NO4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=JIvlWFcmOEM6QVLUyjj6OEK5pdYqitI6evdVqxjsPhUl8aa7V09wKdtrLESjApAE+R nwo1ptDqXUderU63+jMxYfhY0kKGKdNztcFDRVWe7Bf4cQHgH9tPhdNg4HQoVh39dMaQ 3YnLsRCHHsjTuhttRlPaFImCugQK7cHovXcFY= MIME-Version: 1.0 Received: by 10.86.92.9 with SMTP id p9mr3155664fgb.64.1247485549807; Mon, 13 Jul 2009 04:45:49 -0700 (PDT) In-Reply-To: <200907131134.14335.gary.stainburn@ringways.co.uk> References: <200907131134.14335.gary.stainburn@ringways.co.uk> Date: Mon, 13 Jul 2009 12:45:49 +0100 Message-ID: Subject: Re: simple join is beating me From: Oliveiros To: Gary Stainburn Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd28d140f0784046e94dd6e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200907/63 X-Sequence-Number: 32804 --000e0cd28d140f0784046e94dd6e Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 > 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 > --000e0cd28d140f0784046e94dd6e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Howdy, Gary,

I have not the database in this computer, so I cannot t= est the sql I'm sending you, but
if you do an outer join won't i= t result in what you need? Maybe I am not reaching what you want to do...
SELECT deliveryQuery.o_date , orders, delivery
FROM (/* ur first que= ry here */) ordersQuery
NATURAL RIGHT JOIN (/* ur second query goes here= */) deliveryQuery
ORDER BY deliveryQuery.o_date DESC

Tararabite,=

Oliveiros
@Allgarve
=A0


20= 09/7/13 Gary Stainburn <gary.stainburn@ringways.co.uk><= /span>
hi folks

i have the following:

select o_ord_date as o_date, count(o_id) as orders
=A0 =A0from orders where o_de_id in (5,6) and o_ord_date > CURRENT_DATE= -'1
month'::interval
=A0 =A0group by o_ord_date
order by o_date desc

and

select o_act_del_date as o_date, count(o_id) as delivery
=A0 =A0from orders
=A0 =A0where o_de_id in (5,6) and
=A0 =A0 =A0 =A0 =A0o_act_del_date > CURRENT_DATE-'1 month'::int= erval and
=A0 =A0 =A0 =A0 =A0o_act_del_date <=3D CURRENT_DATE
=A0 =A0group by o_act_del_date
order by o_date desc

These give me

=A0 o_date =A0 | orders
------------+--------
=A02009-07-10 | =A0 =A0 =A04
=A02009-07-09 | =A0 =A0 =A05
=A02009-07-08 | =A0 =A0 12
=A02009-07-07 | =A0 =A0 =A05
=A02009-07-06 | =A0 =A0 =A02
=A02009-07-03 | =A0 =A0 =A02
=A02009-07-02 | =A0 =A0 =A07
=A02009-07-01 | =A0 =A0 19
=A02009-06-30 | =A0 =A0 20
=A02009-06-29 | =A0 =A0 28

and

=A0 o_date =A0 | delivery
------------+----------
=A02009-07-13 | =A0 =A0 =A0 =A05
=A02009-07-10 | =A0 =A0 =A0 =A03
=A02009-07-09 | =A0 =A0 =A0 =A04
=A02009-07-08 | =A0 =A0 =A0 =A02
=A02009-07-07 | =A0 =A0 =A0 =A04
=A02009-07-06 | =A0 =A0 =A0 =A07
=A02009-07-03 | =A0 =A0 =A0 =A06
=A02009-07-02 | =A0 =A0 =A0 =A05
=A02009-07-01 | =A0 =A0 =A0 =A03
=A02009-06-30 | =A0 =A0 =A0 =A03

How do i get

=A0 o_date =A0 | orders | delivery
------------+--------+----------
=A02009-07-13 | =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A05
=A02009-07-10 | =A0 =A0 =A04 | =A0 =A0 =A0 =A03
=A02009-07-09 | =A0 =A0 =A05 | =A0 =A0 =A0 =A04
=A02009-07-08 | =A0 =A0 12 | =A0 =A0 =A0 =A02
=A02009-07-07 | =A0 =A0 =A05 | =A0 =A0 =A0 =A04
=A02009-07-06 | =A0 =A0 =A02 | =A0 =A0 =A0 =A07
=A02009-07-03 | =A0 =A0 =A02 | =A0 =A0 =A0 =A06
=A02009-07-02 | =A0 =A0 =A07 | =A0 =A0 =A0 =A05
=A02009-07-01 | =A0 =A0 19 | =A0 =A0 =A0 =A03
=A02009-06-30 | =A0 =A0 20 | =A0 =A0 =A0 =A03
--
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

--000e0cd28d140f0784046e94dd6e--