Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0GGRaV43927 for ; Wed, 16 Jan 2002 11:27:36 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 621107; Wed, 16 Jan 2002 08:27:39 -0800 From: "Josh Berkus" Subject: Re: reforming query for 7.0.2 To: Markus Bertheau , postgres sql list X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Wed, 16 Jan 2002 08:27:39 -0800 Message-ID: In-Reply-To: <1011187735.27679.11.camel@entwicklung01.cenes.de> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200201/191 X-Sequence-Number: 6277 Markus, > how do I reform this query to work with 7.0.2? Better question: Why are you working with 7.0.2? Even the mass-market Linux distros (like Red Hat and SuSE) now come with 7.1.x. > select * from personen join (select count(personen_id), personen_id from > orders group by personen_id) as ordertemp on personen.personen_id = > ordertemp.personen_id Acutally, this query needs some reforming on its own. While it will work, the following version will achieve the same result, much faster, in 7.1 (and 7.2, for that matter): SELECT personen.field1, personen.field2, personen.field3, count(order_id) as no_orders FROM personen LEFT OUTER JOIN orders ON personen.personen_id = orders.personen_id GROUP BY personen.field1, personen.field2, personen.field3 ... you see, your subselect above is completely unnecessary. And slower than a LEFT OUTER JOIN. Simplicity, simplicity, simplicity! Now, we have the problem of no LEFT JOIN support in 7.0, so: SELECT personen.field1, personen.field2, personen.field3, count(order_id) as no_orders FROM personen JOIN orders ON personen.personen_id = orders.personen_id GROUP BY personen.field1, personen.field2, personen.field3 UNION SELECT personen.field1, personen.field2, personen.field3, 0 as no_orders FROM personen WHERE NOT EXISTS (SELECT personen_id FROM orders WHERE personen_id = personen.personen_id); Share & Enjoy! -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco