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