Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0C0E1633E01 for ; Mon, 22 Feb 2010 13:44:26 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 96084-05 for ; Mon, 22 Feb 2010 17:44:15 +0000 (UTC) X-Greylist: delayed 00:29:07.21336 by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 2F730633DE1 for ; Mon, 22 Feb 2010 13:44:14 -0400 (AST) Received: by bwz8 with SMTP id 8so1962939bwz.3 for ; Mon, 22 Feb 2010 09:44:13 -0800 (PST) MIME-Version: 1.0 Received: by 10.102.211.34 with SMTP id j34mr114481mug.5.1266858906403; Mon, 22 Feb 2010 09:15:06 -0800 (PST) In-Reply-To: <4B82B960.3060901@gmail.com> References: <302670f21002211525r65caee9et396c374a3cd64b0d@mail.gmail.com> <4B8211B2.6020406@gmail.com> <302670f21002220751h6cc363fiad449314d961fcc0@mail.gmail.com> <4B82B960.3060901@gmail.com> Date: Mon, 22 Feb 2010 12:15:06 -0500 Message-ID: Subject: Re: Join Advice and Assistance From: Stephen Belcher To: Rob Sargent Cc: Gary Chambers , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001636417c4718c4af0480339307 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/87 X-Sequence-Number: 33686 --001636417c4718c4af0480339307 Content-Type: text/plain; charset=ISO-8859-1 To expand on Rob's reply: If you want to return a single row for each user, regardless of the number of email addresses, you might use ARRAY() with a subquery, eg (haven't tested this to make sure it completely works): SELECT u.*, um.*, ARRAY(SELECT emailaddr FROM user_emailaddrs em WHERE em.userid = u.userid AND em.is_active) AS email_addresses FROM users u INNER JOIN usermetas um ON u.userid = um.userid; Of course, this will return the addresses as a character varying[], with output like {user@domain.tld,user@domain.tld}, and would require some minor contortions to present it to users cleanly. The array_to_string function may help you make it easier to display the results. http://www.postgresql.org/docs/8.4/static/functions-array.html#ARRAY-FUNCTIONS-TABLE Hope this helps, --Stephen Belcher On Mon, Feb 22, 2010 at 12:05 PM, Rob Sargent wrote: > My mistake. Should answer these things late at night. > > I think you will find that arrays will be your friend[s] > > > On 02/22/2010 08:51 AM, Gary Chambers wrote: > >> Rob, >> >> Thanks for the reply... >> >> If you want records for user without email addresses you will need an >>> outer >>> join on user_emailaddrs >>> >>> /* untested */ >>> select u.userId, u.lname, u.lastname ,m.startdate, a.emailaddr >>> from users u >>> join usermetas m on u.userid = m.userid >>> left join user_emailaddrs a on m.userid = a.userid >>> >> >> My question was related more toward eliminating the query returning a >> record for each record in the one-to-many table. I see now that I'm >> going to have to aggregate the e-mail addresses in order to return a >> single row. Thanks again. >> >> -- Gary Chambers >> >> /* Nothing fancy and nothing Microsoft! */ >> > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > --001636417c4718c4af0480339307 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable To expand on Rob's reply:

If you want to return a si= ngle row for each user, regardless of the number of email addresses, you mi= ght use ARRAY() with a subquery, eg (haven't tested this to make sure i= t completely works):

SELECT u.*, um.*, ARRAY(SELECT emailaddr FROM user_emailaddr= s em WHERE em.userid =3D u.userid AND em.is_active) AS email_addresses
FROM users u INNER JOIN usermetas um ON u.userid =3D um.userid;

Of course, this will return the addresses as a characte= r varying[], with output like {user@domain.tld,user@domain.tld}, and would = require some minor contortions to present it to users cleanly. The array_to= _string function may help you make it easier to display the results.


Hope this helps,
--Stephen Belcher

On Mon, Feb 22, 2010 at 12:05 PM, Rob Sargent = <robjsargent@gmail.com><= /span> wrote:
My mistake. =A0Should answer these things l= ate at night.

I think you will find that arrays will be your friend[s]


On 02/22/2010 08:51 AM, Gary Chambers wrote:
Rob,

Thanks for the reply...

If you want records for user without email addresses you will need an outer=
join on user_emailaddrs

/* untested */
select u.userId, u.lname, u.lastname ,m.startdate, a.emailaddr
from users u
join usermetas m on u.userid =3D m.userid
left join user_emailaddrs a on m.userid =3D a.userid

My question was related more toward eliminating the query returning a
record for each record in the one-to-many table. =A0I see now that I'm<= br> going to have to aggregate the e-mail addresses in order to return a
single row. =A0Thanks again.

-- Gary Chambers

/* Nothing fancy and nothing Microsoft! */

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql

--001636417c4718c4af0480339307--