pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with simple SQL query?
4+ messages / 3 participants
[nested] [flat]
* Help with simple SQL query?
@ 2005-10-05 11:35 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 0 replies; 4+ messages in thread
From: Joost Kraaijeveld @ 2005-10-05 11:35 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have 2 tables with the following columns:
order: order_id, order_price
orderline: parent_order_id, orderline_price
I want all orders order where _price <> sum(orderline_price).
What is wrong with the following query:
select order_id from order, orderline
where order_id = parent_order_id
and order_price <> (select sum(orderline_price) from orderline group by
parent_order_id)
It reports "ERROR: more than one row returned by a subquery used as an
expression" which seems right (the select sum() returns multiple rows?),
but I cannot get query right. Can someone help?
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help with simple SQL query?
@ 2005-10-05 11:47 Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 1 reply; 4+ messages in thread
From: Gregory S. Williamson @ 2005-10-05 11:47 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; pgsql-sql
Joost --
You are correct in stating that the problem is that the subquery returns more than 1 row -- try using the NOT IN syntax ... it is not likely to be very efficient but at least avoids the syntax error:
select order_id from order, orderline
where order_id = parent_order_id
and order_price NOT IN (select sum(orderline_price) from orderline group by
parent_order_id)
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Joost Kraaijeveld
Sent: Wed 10/5/2005 4:35 AM
To: Pgsql-sql@postgresql.org
Cc:
Subject: [SQL] Help with simple SQL query?
Hi,
I have 2 tables with the following columns:
order: order_id, order_price
orderline: parent_order_id, orderline_price
I want all orders order where _price <> sum(orderline_price).
What is wrong with the following query:
select order_id from order, orderline
where order_id = parent_order_id
and order_price <> (select sum(orderline_price) from orderline group by
parent_order_id)
It reports "ERROR: more than one row returned by a subquery used as an
expression" which seems right (the select sum() returns multiple rows?),
but I cannot get query right. Can someone help?
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
!DSPAM:4343bb5c106941059188129!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help with simple SQL query?
@ 2005-10-05 12:21 Frank van Vugt <ftm.van.vugt@foxi.nl>
parent: Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 1 reply; 4+ messages in thread
From: Frank van Vugt @ 2005-10-05 12:21 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@askesis.nl>; +Cc: pgsql-sql
Hi Joost,
Try joining and comparing the order table with/to an aggregated orderline
table, something like this:
select order_id, order_price, sum_price, order_price - sum_price as diff
from order, (select parent_order_id, sum(orderline_price) as sum_price
from orderline group by parent_order_id) as foo
where order_id = parent_order_id
and order_price != sum_price
This should prove to be as efficient as it gets ;)
--
Best,
Frank.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help with simple SQL query?
@ 2005-10-05 13:19 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
parent: Frank van Vugt <ftm.van.vugt@foxi.nl>
0 siblings, 0 replies; 4+ messages in thread
From: Joost Kraaijeveld @ 2005-10-05 13:19 UTC (permalink / raw)
To: Frank van Vugt <ftm.van.vugt@foxi.nl>; +Cc: pgsql-sql
On Wed, 2005-10-05 at 14:21 +0200, Frank van Vugt wrote:
> Hi Joost,
>
> Try joining and comparing the order table with/to an aggregated orderline
> table, something like this:
>
> select order_id, order_price, sum_price, order_price - sum_price as diff
> from order, (select parent_order_id, sum(orderline_price) as sum_price
> from orderline group by parent_order_id) as foo
> where order_id = parent_order_id
> and order_price != sum_price
>
>
> This should prove to be as efficient as it gets ;)
How nice. An answer between the questions. And a working anser!. Which
manual did I miss ;-)
Thanks.
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-10-05 13:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-05 11:35 Help with simple SQL query? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2005-10-05 11:47 Re: Help with simple SQL query? Gregory S. Williamson <gsw@globexplorer.com>
2005-10-05 12:21 ` Frank van Vugt <ftm.van.vugt@foxi.nl>
2005-10-05 13:19 ` Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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