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
thatis privileged, confidential or otherwise protected from disclosure. Dissemination, distribution or copying of this
e-mailor any attachments by anyone other than the intended recipient, or an employee or agent responsible for
deliveringthe message to the intended recipient, is prohibited. If you are not the intended recipient of this message
orthe employee or agent responsible for delivery of this email to the intended recipient, please notify the sender by
replyingto this message and then delete it from your system. Any use, dissemination, distribution, or reproduction of
thismessage by unintended recipients is strictly prohibited and may be unlawful.
