Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7BFF263287A for ; Mon, 13 Jul 2009 11:02:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 20352-05 for ; Mon, 13 Jul 2009 11:02:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.159]) by mail.postgresql.org (Postfix) with ESMTP id 46243632418 for ; Mon, 13 Jul 2009 11:02:30 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id e12so671876fga.0 for ; Mon, 13 Jul 2009 07:02:28 -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=d6x0b0eadbFi3fkwRpWSGEzkqSrM5V8oZr/6+6qK4WM=; b=ZdmbH9u05c+B0pACLgSafKZsAcCH1hSJMdT5tYJPmdtqiXw9hg7F6LPMF8Ij4yjgxq TYjYv8qiZ23D+9OwpEEHiybC6bTPhS4hScA27dbwUSVe8XzvieIdtQexkiqmiA3ZrwnB GDF62kWw9WIshSV6Ve7gCUMS4S8WTLbIFu5Cc= 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=fGT8QziopaEgr+u/iXZgRDk0tKkLIrryFuz3beLZ+c4KudgNZcpOuR3p2GwgzzXGhq gH1FdM0B3RWRdLy1JBG4Am/1ld98Iu4PiTEidhd9rcKasdVxsQy9EPQXQZsS1E+Ue9HE WwZXZYsRLqkXt4FmSIQWGULLX4qI20Ix9onrI= MIME-Version: 1.0 Received: by 10.86.66.20 with SMTP id o20mr3316663fga.15.1247493748175; Mon, 13 Jul 2009 07:02:28 -0700 (PDT) In-Reply-To: <200907131412.04729.gary.stainburn@ringways.co.uk> References: <200907131134.14335.gary.stainburn@ringways.co.uk> <200907131412.04729.gary.stainburn@ringways.co.uk> Date: Mon, 13 Jul 2009 15:02:28 +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=000e0cd24ba8b83325046e96c598 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/66 X-Sequence-Number: 32807 --000e0cd24ba8b83325046e96c598 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Oh, I actually thought that it was the behavior you wanted, Gary. On the example you supplied you have an order on June , the 29th and it doesn't appear on the result you showed. You wanted this row do appear as an NULL delivery? Just try replacing the RIGHT JOIN by FULL JOIN. This will cause all dates to appear that either have orders or deliveries, or both. I thought that you needed the days without orders to appear, but not the days without deliveries, as you didn't include June the 29th on your desired result. But it's not serious, If I understand correctly. Just replace the RIGHT by FULL Best, Oliveiros 2009/7/13 Gary Stainburn > 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 > > > > > 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 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > --000e0cd24ba8b83325046e96c598 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Oh, I actually thought that it was the behavior you wanted, Gary.

On= the example you supplied you have an order on June , the 29th and it doesn= 't
appear on the result you showed.
You wanted this row do appear= as an NULL delivery?

Just try replacing the RIGHT JOIN
by FULL JOIN.

This will ca= use all dates to appear that either have orders or deliveries, or both.
=
I thought that you needed the days without orders to appear, but not th= e days without deliveries,
as you didn't include June the 29th on your desired result.

But = it's not serious, If I understand correctly.
Just replace the RIGHT = by FULL

Best,
Oliveiros

2009/7/= 13 Gary Stainburn <gary.stainburn@ringways.co.uk>
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&#= 39;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
> > =A0 =A0from orders where o_de_id in (5,6) and o_ord_date > CUR= RENT_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&= #39;::interval 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 i= t
> > 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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql

--000e0cd24ba8b83325046e96c598--