Re: simple join is beating me - Mailing list pgsql-sql
| From | Oliveiros |
|---|---|
| Subject | Re: simple join is beating me |
| Date | |
| Msg-id | f54607780907130702u6af5b65ax4d0951cf513944f2@mail.gmail.com Whole thread Raw |
| In response to | Re: simple join is beating me (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
| List | pgsql-sql |
Oh, I actually thought that it was the behavior you wanted, Gary.
On the example you supplied you have an order on June , the 29th and it doesn't
appear on the result you showed.
You wanted this row do appear as an NULL delivery?
Just try replacing the RIGHT JOIN
by FULL JOIN.
This will cause all dates to appear that either have orders or deliveries, or both.
I thought that you needed the days without orders to appear, but not the days without deliveries,
as you didn't include June the 29th on your desired result.
But it's not serious, If I understand correctly.
Just replace the RIGHT by FULL
Best,
Oliveiros
On the example you supplied you have an order on June , the 29th and it doesn't
appear on the result you showed.
You wanted this row do appear as an NULL delivery?
Just try replacing the RIGHT JOIN
by FULL JOIN.
This will cause all dates to appear that either have orders or deliveries, or both.
I thought that you needed the days without orders to appear, but not the days without deliveries,
as you didn't include June the 29th on your desired result.
But it's not serious, If I understand correctly.
Just replace the RIGHT by FULL
Best,
Oliveiros
2009/7/13 Gary Stainburn <gary.stainburn@ringways.co.uk>
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-sqlGary 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