X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D558E475E22 for ; Wed, 19 Feb 2003 16:07:51 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 52501-10 for ; Wed, 19 Feb 2003 16:07:41 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id CE735475C22 for ; Wed, 19 Feb 2003 15:49:52 -0500 (EST) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2847866; Wed, 19 Feb 2003 12:50:01 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "Matthew Nuzum" , Subject: Re: simple join problem Date: Wed, 19 Feb 2003 12:49:26 -0800 X-Mailer: KMail [version 1.4] References: <000501c2d848$002317e0$6900a8c0@mattspc> In-Reply-To: <000501c2d848$002317e0$6900a8c0@mattspc> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200302191249.26342.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/439 X-Sequence-Number: 11837 Matthew, > Can anyone demonstrate the correct syntax for joining several tables in t= his > way? Actually, there's several possible answers to your problem. Here's one: >=20 > 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 AND=20 users_address.primary =3D TRUE)))=20 > LEFT JOIN users_email ON ((users.uid =3D users_email.uid and=20 users_email.primary =3D TRUE)))=20 > LEFT JOIN users_phone ON ((users.uid =3D users_phone.uid and=20 users_phone.primary =3D TRUE))) ; It's a neat trick that join conditions can be *any* expression that evaluat= es=20 to a boolean value. --=20 -Josh Berkus Aglio Database Solutions San Francisco