Re: simple join is beating me

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(dot)stainburn(at)ringways(dot)co(dot)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(at)postgresql(dot)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