Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "Matthew Nuzum" <cobalt@bearfruit.org>,
	<pgsql-sql@postgresql.org>
Subject: Re: simple join problem
Date: Wed, 19 Feb 2003 20:08:00 +0000
User-Agent: KMail/1.4.3
References: <000501c2d848$002317e0$6900a8c0@mattspc>
In-Reply-To: <000501c2d848$002317e0$6900a8c0@mattspc>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302192008.00761.dev@archonet.com>

On Wednesday 19 Feb 2003 6:51 pm, Matthew Nuzum wrote:
> 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 =3D users_address.uid)))
> 	LEFT JOIN users_email ON ((users.uid =3D users_email.uid)))
> 	LEFT JOIN users_phone ON ((users.uid =3D users_phone.uid)))
> WHERE (((users_address."primary" =3D 't'::bool)
>   AND (users_email."primary" =3D 't'::bool))
>   AND (users_phone."primary" =3D 't'::bool));

If there isn't data in all tables users_email.primary can't be "t" can it? =
Try=20
adding OR IS NULL inside the brackets and see if that does what you want.

--=20
  Richard Huxton