X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BD029475F27 for ; Wed, 19 Feb 2003 14:52:52 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 23503-09 for ; Wed, 19 Feb 2003 14:52:42 -0500 (EST) Received: from ms-smtp-01.tampabay.rr.com (ms-smtp-01.tampabay.rr.com [65.32.1.43]) by postgresql.org (Postfix) with ESMTP id E67C5475CCE for ; Wed, 19 Feb 2003 14:51:43 -0500 (EST) Received: from mattspc (169-52.34-65.tampabay.rr.com [65.34.52.169]) by ms-smtp-01.tampabay.rr.com (8.12.5/8.12.5) with ESMTP id h1JIqGKm013140 for ; Wed, 19 Feb 2003 13:52:17 -0500 (EST) From: "Matthew Nuzum" To: Subject: simple join problem Date: Wed, 19 Feb 2003 13:51:58 -0500 Organization: Bearfruit.org Message-ID: <000501c2d848$002317e0$6900a8c0@mattspc> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/433 X-Sequence-Number: 11831 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:=20 SELECT=20 users.uid, users.loginid, users."password", users.title, users.firstname, users.middlename, users.lastname, users.suffix,=20 users.organization, users.job_title, users_address.address1,=20 users_address.address2, users_address.address3, users_address.city, users_address.state, users_address.zip, users_address.country,=20 users_email.email, users_phone.phone=20 FROM (((users=20 LEFT JOIN users_address ON ((users.uid =3D users_address.uid)))=20 LEFT JOIN users_email ON ((users.uid =3D users_email.uid)))=20 LEFT JOIN users_phone ON ((users.uid =3D users_phone.uid)))=20 WHERE (((users_address."primary" =3D 't'::bool)=20 AND (users_email."primary" =3D 't'::bool))=20 AND (users_phone."primary" =3D 't'::bool)); I doubt you need the following information, but if you do, here are the table definitions: Table "users" Column | Type | Modifiers=20=20=20=20=20=20=20= =20=20=20=20 --------------+-----------------------+----------------------------- 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=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20 -------------+---------+----------------------------- 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=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 -------------+---------+----------------------------- 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=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20 -------------+---------+----------------------------- 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