DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=svyMIaSOX0w4yYvJNAGxFxo2SruOODF5ClrOXoKj88OHlEq/aD3JBNLjectORwEN17
	kn0MivFEpfQLlx1x8NfF65cd2lPrW8KxDUSD/a9QARMMX3Ea2Z8zxDwPpesNMxLxX+KC
	yoJp048AMASU2Oct8hx5yMqgjr5qE163DtHr0=
MIME-Version: 1.0
Date: Sun, 21 Feb 2010 18:25:24 -0500
Message-ID: <302670f21002211525r65caee9et396c374a3cd64b0d@mail.gmail.com>
Subject: Join Advice and Assistance
From: Gary Chambers <gwchamb@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

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