Thread: Join Advice and Assistance
All, I've encountered a mental block due primarily to my inexperience with moderately complex joins. Given the following three tables: Table "public.users" Column | Type | Modifiers -----------+------------------------+-----------------------userid | bigint | not nulllname | charactervarying(64) | not nullfname | character varying(64) | not nullpasswd | character varying(64) | not nullis_active| boolean | not null default true Table "public.user_emailaddrs" Column | Type | Modifiers -----------+------------------------+-----------------------userid | bigint | not nullemailaddr | charactervarying(256) | not nullis_active | boolean | not null default true Table "public.usermetas" Column | Type | Modifiers ----------------+-----------------------------+------------------------userid | bigint | notnullstartdate | timestamp without time zone | not null default now()lastlogindate | timestamp without time zone| not null default now()lastpwchange | timestamp without time zone | not null default now()logincount | integer | not null default 1 users and usermetas is a one-to-one relationship. users and user_emailaddrs is a one-to-many relationship. What is the best way to get these tables joined on userid and return all emailaddr records from user_emailaddrs (e.g. if userid has three (3) e-mail addresses in user_emailaddrs)? Is there any way to avoid returning all fields in triplicate? Please feel free to criticize where necessary. Thank you very much in advance. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */
Gary Chambers wrote: > All, > > I've encountered a mental block due primarily to my inexperience with > moderately complex joins. Given the following three tables: > > Table "public.users" > Column | Type | Modifiers > -----------+------------------------+----------------------- > userid | bigint | not null > lname | character varying(64) | not null > fname | character varying(64) | not null > passwd | character varying(64) | not null > is_active | boolean | not null default true > > Table "public.user_emailaddrs" > Column | Type | Modifiers > -----------+------------------------+----------------------- > userid | bigint | not null > emailaddr | character varying(256) | not null > is_active | boolean | not null default true > > Table "public.usermetas" > Column | Type | Modifiers > ----------------+-----------------------------+------------------------ > userid | bigint | not null > startdate | timestamp without time zone | not null default now() > lastlogindate | timestamp without time zone | not null default now() > lastpwchange | timestamp without time zone | not null default now() > logincount | integer | not null default 1 > > users and usermetas is a one-to-one relationship. > users and user_emailaddrs is a one-to-many relationship. > > What is the best way to get these tables joined on userid and return > all emailaddr records from user_emailaddrs (e.g. if userid has three > (3) e-mail addresses in user_emailaddrs)? Is there any way to avoid > returning all fields in triplicate? Please feel free to criticize > where necessary. Thank you very much in advance. > > -- Gary Chambers > > /* Nothing fancy and nothing Microsoft! */ > > 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
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! */
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! */
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
Stephen, > 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): Your query worked perfectly! > 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. Absolutely -- and thank you for the suggestion. I'll be retrieving the results of the query through PHP, so cleanup in the query may even be a performance degradation. > Hope this helps, You and Rob Sargent have helped a great deal. Thanks to both of you. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */