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.
Hope this helps,
--Stephen Belcher
On Mon, Feb 22, 2010 at 12:05 PM, Rob Sargent
<robjsargent@gmail.com> 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