Обсуждение: 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! */

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! */