Date: Wed, 19 Feb 2003 12:25:23 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Matthew Nuzum <cobalt@bearfruit.org>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: simple join problem
In-Reply-To: <000501c2d848$002317e0$6900a8c0@mattspc>
Message-ID: <20030219121921.F36825-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Wed, 19 Feb 2003, 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 = 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));

The where clause undoes the LEFT JOIN.

Maybe something like:
SELECT ...
 FROM users LEFT JOIN (select * from users_address where primary='t') AS
  users_address  ON (users.uid=users_address.uid) ...

I'd thought about just changing the WHERE clause elements to something
like:
 (users_address.uaid IS NULL OR users_address."primary" = 't'::bool)

but that'll do the wrong thing if there are matching address but none are
primary (it shouldn't happen presumably, but I don't see anything that
stops it in the table descriptions -- I also don't know if there's an
intention of having multiple primary addresses which I guess could happen
unless primary is part of the pkey for those tables - which would prevent
multiple secondaries, so I assume it isn't)