Re: simple join is beating me

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
>