Join Advice and Assistance

From: Gary Chambers <gwchamb(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Join Advice and Assistance
Date: 2010-02-21 23:25:24
Message-ID: 302670f21002211525r65caee9et396c374a3cd64b0d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rob Sargent 2010-02-22 05:10:10 Re: Join Advice and Assistance
Previous Message Tom Lane 2010-02-19 14:28:55 Re: sintax error