pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsimple join is beating me
5+ messages / 3 participants
[nested] [flat]
* simple join is beating me
@ 2009-07-13 10:34 Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-07-13 11:45 ` Re: simple join is beating me Oliveiros <oliveiros.cristina@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2009-07-13 10:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: simple join is beating me
2009-07-13 10:34 simple join is beating me Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2009-07-13 11:45 ` Oliveiros <oliveiros.cristina@gmail.com>
2009-07-13 13:12 ` Re: simple join is beating me Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Oliveiros @ 2009-07-13 11:45 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: simple join is beating me
2009-07-13 10:34 simple join is beating me Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-07-13 11:45 ` Re: simple join is beating me Oliveiros <oliveiros.cristina@gmail.com>
@ 2009-07-13 13:12 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-07-13 13:23 ` Re: simple join is beating me Hartman, Matthew <Matthew.Hartman@krcc.on.ca>
2009-07-13 14:02 ` Re: simple join is beating me Oliveiros <oliveiros.cristina@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Gary Stainburn @ 2009-07-13 13:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: simple join is beating me
2009-07-13 10:34 simple join is beating me Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-07-13 11:45 ` Re: simple join is beating me Oliveiros <oliveiros.cristina@gmail.com>
2009-07-13 13:12 ` Re: simple join is beating me Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2009-07-13 13:23 ` Hartman, Matthew <Matthew.Hartman@krcc.on.ca>
1 sibling, 0 replies; 5+ messages in thread
From: Hartman, Matthew @ 2009-07-13 13:23 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
> 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 (
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: simple join is beating me
2009-07-13 10:34 simple join is beating me Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-07-13 11:45 ` Re: simple join is beating me Oliveiros <oliveiros.cristina@gmail.com>
2009-07-13 13:12 ` Re: simple join is beating me Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2009-07-13 14:02 ` Oliveiros <oliveiros.cristina@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Oliveiros @ 2009-07-13 14:02 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: 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
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-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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-07-13 14:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-13 10:34 simple join is beating me Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-07-13 11:45 ` Oliveiros <oliveiros.cristina@gmail.com>
2009-07-13 13:12 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-07-13 13:23 ` Hartman, Matthew <Matthew.Hartman@krcc.on.ca>
2009-07-13 14:02 ` Oliveiros <oliveiros.cristina@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox