From: Matthew Nuzum <cobalt@bearfruit.org>
To: pgsql-sql@postgresql.org
Subject: simple join problem
Date: Wed, 19 Feb 2003 13:51:58 -0500
Message-ID: <000501c2d848$002317e0$6900a8c0@mattspc> (raw)
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 default nextval(...
loginid | character varying(12) | not null
password | character varying(64) | not null
title | character varying(10) |
firstname | text | not null
middlename | 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 null
primary | 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 null
primary | 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
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: cobalt@bearfruit.org
Subject: Re: simple join problem
In-Reply-To: <000501c2d848$002317e0$6900a8c0@mattspc>
* 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