Thread: simple join is beating me

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 |      42009-07-09 |      52009-07-08 |     122009-07-07 |      52009-07-06 |
22009-07-03|      22009-07-02 |      72009-07-01 |     192009-06-30 |     202009-06-29 |     28
 

and 
  o_date   | delivery
------------+----------2009-07-13 |        52009-07-10 |        32009-07-09 |        42009-07-08 |        22009-07-07 |
      42009-07-06 |        72009-07-03 |        62009-07-02 |        52009-07-01 |        32009-06-30 |        3
 

How do i get
  o_date   | orders | delivery
------------+--------+----------2009-07-13 |        |        52009-07-10 |      4 |        32009-07-09 |      5 |
42009-07-08 |     12 |        22009-07-07 |      5 |        42009-07-06 |      2 |        72009-07-03 |      2 |
62009-07-02|      7 |        52009-07-01 |     19 |        32009-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    

Howdy, Gary,<br /><br />I have not the database in this computer, so I cannot test the sql I'm sending you, but<br />if
youdo an outer join won't it result in what you need? Maybe I am not reaching what you want to do...<br /><br />SELECT
deliveryQuery.o_date, orders, delivery<br />FROM (/* ur first query here */) ordersQuery<br />NATURAL RIGHT JOIN (/* ur
secondquery goes here */) deliveryQuery<br />ORDER BY deliveryQuery.o_date DESC<br /><br />Tararabite,<br /><br
/>Oliveiros<br />@Allgarve<br /> <br /><br /><br /><div class="gmail_quote">2009/7/13 Gary Stainburn <span
dir="ltr"><<ahref="mailto:gary.stainburn@ringways.co.uk"
target="_blank">gary.stainburn@ringways.co.uk</a>></span><br/><blockquote class="gmail_quote" style="border-left:
1pxsolid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> hi folks<br /><br /> i have the
following:<br/><br /> select o_ord_date as o_date, count(o_id) as orders<br />    from orders where o_de_id in (5,6)
ando_ord_date > CURRENT_DATE-'1<br /> month'::interval<br />    group by o_ord_date<br /> order by o_date desc<br
/><br/> and<br /><br /> select o_act_del_date as o_date, count(o_id) as delivery<br />    from orders<br />    where
o_de_idin (5,6) and<br />          o_act_del_date > CURRENT_DATE-'1 month'::interval and<br />        
 o_act_del_date<= CURRENT_DATE<br />    group by o_act_del_date<br /> order by o_date desc<br /><br /> These give
me<br/><br />   o_date   | orders<br /> ------------+--------<br />  2009-07-10 |      4<br />  2009-07-09 |      5<br
/> 2009-07-08 |     12<br />  2009-07-07 |      5<br />  2009-07-06 |      2<br />  2009-07-03 |      2<br />
 2009-07-02|      7<br />  2009-07-01 |     19<br />  2009-06-30 |     20<br />  2009-06-29 |     28<br /><br /> and<br
/><br/>   o_date   | delivery<br /> ------------+----------<br />  2009-07-13 |        5<br />  2009-07-10 |      
 3<br/>  2009-07-09 |        4<br />  2009-07-08 |        2<br />  2009-07-07 |        4<br />  2009-07-06 |      
 7<br/>  2009-07-03 |        6<br />  2009-07-02 |        5<br />  2009-07-01 |        3<br />  2009-06-30 |      
 3<br/><br /> How do i get<br /><br />   o_date   | orders | delivery<br /> ------------+--------+----------<br />
 2009-07-13|        |        5<br />  2009-07-10 |      4 |        3<br />  2009-07-09 |      5 |        4<br />
 2009-07-08|     12 |        2<br />  2009-07-07 |      5 |        4<br />  2009-07-06 |      2 |        7<br />
 2009-07-03|      2 |        6<br />  2009-07-02 |      7 |        5<br />  2009-07-01 |     19 |        3<br />
 2009-06-30|     20 |        3<br /> --<br /> Gary Stainburn<br /><br /> This email does not contain private or
confidentialmaterial as it<br /> may be snooped on by interested government parties for unknown<br /> and undisclosed
purposes- Regulation of Investigatory Powers Act, 2000<br /><font color="#888888"><br /> --<br /> Sent via pgsql-sql
mailinglist (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br /> To make
changesto your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /> 

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     

> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Gary Stainburn
> Sent: Monday, July 13, 2009 9:12 AM
>
> 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.

Try this. Obviously it can be cleaned up, and it's not terribly
efficient in it's current form, but you'll get the idea.

To be fair, your sample output did not account for this.. You showed one
example where one of the two columns could be null, not both.

select    myDate, orders, delivery
from        (        select distinct o_ord_date as myDate        from orders        where o_de_id in (5,6) and
o_ord_date> 
CURRENT_DATE-'1 month'::interval        union all        select distinct o_act_del_date 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    ) as q1    left join (        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    ) as q2 on q2.o_date = q1.myDate    left join (
selecto_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    ) as q3 on q3.o_date =
q1.myDate

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
.now.