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.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