Re: Subqueries - performance and use question - Mailing list pgsql-general

try this approach:

SELECT
  c.id,
  c.firstname,
  c.lastname,
  a.latest_billdate
FROM
  customers c
INNER JOIN -- or LEFT if you want the NULLs
(
  SELECT
    customer_id,
    max(billdate) as latest_billdate
  FROM
    ar
) a
ON
  c.customerid = a.customerid
WHERE
  c.status = 'new';



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Demel, Jeff
> Sent: Thursday, February 01, 2007 8:08 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Subqueries - performance and use question
>
> I need some basic advice on how to run a subquery, or if there's a
> better way.  Let me set up a situation, and get some advice
> on it.  This
> is my first post on this list, so I hope this kind of noob question is
> ok.
>
> Say I have a table of customers and table of accounts receivable
> transactions,  There is a one-to-many relationship between the two
> (obviously a customer can have more than one purchase/transaction).
>
> I want to run a query where I pull a set of customers based on some
> parameter like, for sake of an example, where their status = new, and
> also pull the most recent billing date from the accounts receivable
> table.
>
> Here's what I came up with:
>
> SELECT customers.id, customers.firstname,
>   customers.lastname, customers.phone number,
>   (SELECT ar.billdate FROM ar
>      WHERE customers.customerid = ar.customerid
>      ORDER BY ar.billdate LIMIT 1)
>      AS lastarbilldate
> FROM customers
> WHERE customers.status = 'new';
>
> As you can see, I'm using a subquery here to get the latest billing
> date.  My question is twofold.  Is this subquery style the only way to
> get one record in a one-to-many relationship, or is there a
> way to do it
> with a join?  Also, if this is fine, is there a way to do it
> that would
> get the same results but be faster?  It may not matter on a small
> database, but if you've got millions of customers and transactions, a
> subquery can get expensive.
>
> I'm just looking for some basic direction.  I hope my fake
> example makes
> sense.
>
> I'm running PostgreSQL 8.1, on a Windows 2003 server.
>
> TIA
>
> -Jeff
> This email is intended only for the individual or entity to
> which it is addressed.  This email may contain information
> that is privileged, confidential or otherwise protected from
> disclosure. Dissemination, distribution or copying of this
> e-mail or any attachments by anyone other than the intended
> recipient, or an employee or agent responsible for delivering
> the message to the intended recipient, is prohibited. If you
> are not the intended recipient of this message or the
> employee or agent responsible for delivery of this email to
> the intended recipient, please notify the sender by replying
> to this message and then delete it from your system.  Any
> use, dissemination, distribution, or reproduction of this
> message by unintended recipients is strictly prohibited and
> may be unlawful.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>