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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
