Re: Join Advice and Assistance

[Rob Sargent <robjsargent(at)gmail(dot)com>]

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