Join Advice and Assistance

Поиск
Список
Период
Сортировка
От Gary Chambers
Тема Join Advice and Assistance
Дата
Msg-id 302670f21002211525r65caee9et396c374a3cd64b0d@mail.gmail.com
обсуждение исходный текст
Ответы Re: Join Advice and Assistance  (Rob Sargent <robjsargent@gmail.com>)
Список 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 nulllname     |
charactervarying(64)  | not nullfname     | character varying(64)  | not nullpasswd    | character varying(64)  | not
nullis_active| boolean                | not null default true
 
              Table "public.user_emailaddrs" Column   |          Type          |       Modifiers
-----------+------------------------+-----------------------userid    | bigint                 | not nullemailaddr |
charactervarying(256) | not nullis_active | boolean                | not null default true
 
                      Table "public.usermetas"    Column     |            Type             |       Modifiers
----------------+-----------------------------+------------------------userid         | bigint                      |
notnullstartdate      | 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! */


В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: sintax error
Следующее
От: Rob Sargent
Дата:
Сообщение: Re: Join Advice and Assistance