Re: Help with simple SQL query?

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!