simple join problem - Mailing list pgsql-sql

From Matthew Nuzum
Subject simple join problem
Date
Msg-id 000501c2d848$002317e0$6900a8c0@mattspc
Whole thread Raw
Responses Re: simple join problem
Re: simple join problem
Re: simple join problem
List pgsql-sql
Sorry for the simple question, but I'm struggling with a join.

I'm creating a view that will show data from 4 tables.  The problem is, I
want the view to show a record for every entry in the "users" table, even if
there is no matching entry all or some of the other tables.

Right now my view only shows records that have data in all 4 tables.  I know
I've had this problem before and I know there's simple syntax, but I've only
done it with two tables in the join and I (apparently) can't remember the
correct syntax.

Can anyone demonstrate the correct syntax for joining several tables in this
way?

Here's my view definition:
SELECT users.uid, users.loginid, users."password", users.title,users.firstname, users.middlename, users.lastname,
users.suffix,users.organization, users.job_title, users_address.address1, users_address.address2,
users_address.address3,users_address.city,    users_address.state, users_address.zip, users_address.country,
users_email.email,users_phone.phone  
FROM (((users LEFT JOIN users_address ON ((users.uid = users_address.uid))) LEFT JOIN users_email ON ((users.uid =
users_email.uid)))LEFT JOIN users_phone ON ((users.uid = users_phone.uid)))  
WHERE (((users_address."primary" = 't'::bool)  AND (users_email."primary" = 't'::bool))  AND (users_phone."primary" =
't'::bool));

I doubt you need the following information, but if you do, here are the
table definitions:
            Table "users"   Column    |         Type          |    Modifiers
--------------+-----------------------+-----------------------------uid          | integer               | not null
defaultnextval(...loginid      | character varying(12) | not nullpassword     | character varying(64) | not nulltitle
    | character varying(10) |firstname    | text                  | not nullmiddlename   | text
|lastname    | text                  |suffix       | character varying(10) |organization | text
|job_title   | text                  | 
Primary key: users_pkey
             Table "users_address"  Column    |  Type   |   Modifiers
-------------+---------+-----------------------------uaid        | integer | not null default nextval(...uid         |
integer|primary     | boolean | default 't'description | text    |address1    | text    |address2    | text
|address3   | text    |city        | text    |state       | text    |zip         | text    |country     | text    | 
Primary key: users_address_pkey
           Table "users_email"  Column    |  Type   |   Modifiers
-------------+---------+-----------------------------ueid        | integer | not null default nextval(...uid         |
integer|email       | text    | not nullprimary     | boolean | default 't'description | text    | 
Primary key: users_email_pkey
          Table "users_phone"  Column    |  Type   |    Modifiers
-------------+---------+-----------------------------upid        | integer | not null default nextval(...uid         |
integer|phone       | text    | not nullprimary     | boolean | default 't'description | text    | 
Primary key: users_phone_pkey

My View is be:              View "users_detail"   Column    |         Type          | Modifiers
--------------+-----------------------+-----------uid          | integer               |loginid      | character
varying(12)|password     | character varying(64) |title        | character varying(10) |firstname    | text
    |middlename   | text                  |lastname     | text                  |suffix       | character varying(10)
|organization| text                  |job_title    | text                  |address1     | text
|address2    | text                  |address3     | text                  |city         | text                  |state
      | text                  |zip          | text                  |country      | text                  |email
|text                  |phone        | text                  | 

Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org




pgsql-sql by date:

Previous
From: Guy Fraser
Date:
Subject: Re: Passing arrays
Next
From: Richard Huxton
Date:
Subject: Re: simple join problem