DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=Yy3ztPTL2tzmlvQ33L7lt7s4ugKeKI6ja42XYY00X6xKH4V4+LP5r9oAjVt/3mUDxI
	wIU2j2I60/Q55Rfv+NzHp3bE813meijC3TK+DcJlyk4tZf6O3HdrC95hGcT2zFZ1wc6H
	Z5+9maxFWPt1EZJ7YTB41cWs6RGwutnGKt/ww=
Message-ID: <4B8211B2.6020406@gmail.com>
Date: Sun, 21 Feb 2010 22:10:10 -0700
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Gary Chambers <gwchamb@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Join Advice and Assistance
References: <302670f21002211525r65caee9et396c374a3cd64b0d@mail.gmail.com>
In-Reply-To: <302670f21002211525r65caee9et396c374a3cd64b0d@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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