Re: simple join is beating me

> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-
> owner(at)postgresql(dot)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 (
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
) as q3 on q3.o_date = q1.myDate

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