From: Gary Chambers <gwchamb@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Join Advice and Assistance
Date: Sun, 21 Feb 2010 18:25:24 -0500
Message-ID: <302670f21002211525r65caee9et396c374a3cd64b0d@mail.gmail.com> (raw)
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! */
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gwchamb@gmail.com
Subject: Re: Join Advice and Assistance
In-Reply-To: <302670f21002211525r65caee9et396c374a3cd64b0d@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox