pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Markus Bertheau <twanger@bluetwanger.de>
To: postgres sql list <pgsql-sql@postgresql.org>
Subject: Re: reforming query for 7.0.2
Date: Wed, 16 Jan 2002 08:27:39 -0800
Message-ID: <web-621107@davinci.ethosmedia.com> (raw)
In-Reply-To: <1011187735.27679.11.camel@entwicklung01.cenes.de>

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



view thread (3+ messages)  latest in thread

Message-ID: <web-621107@davinci.ethosmedia.com>
Permalink:  ../web-621107@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-621107@davinci.ethosmedia.com

 · 

reply

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: josh@agliodbs.com, twanger@bluetwanger.de
  Subject: Re: reforming query for 7.0.2
  In-Reply-To: <web-621107@davinci.ethosmedia.com>

* 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