Обсуждение: 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
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!
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_pricefrom orderline group by parent_order_id) as foo where order_id = parent_order_idand order_price != sum_price This should prove to be as efficient as it gets ;) -- Best, Frank.
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