From: Gregory S. Williamson <gsw@globexplorer.com>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
To: Pgsql-sql@postgresql.org
Subject: Re: Help with simple SQL query?
Date: Wed, 5 Oct 2005 04:47:17 -0700
Message-ID: <71E37EF6B7DCC1499CEA0316A2568328024BBA0B@loki.wc.globexplorer.net> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gsw@globexplorer.com, J.Kraaijeveld@Askesis.nl
Subject: Re: Help with simple SQL query?
In-Reply-To: <71E37EF6B7DCC1499CEA0316A2568328024BBA0B@loki.wc.globexplorer.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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