From: Richard Poole <richard@ruthie.org>
To: pgsql-sql@postgresql.org
Subject: Re: Left joins with multiple tables
Date: Sun, 18 Jan 2004 21:57:14 +0000
Message-ID: <20040118215714.GA11708@guests.deus.net> (raw)
In-Reply-To: <pan.2004.01.17.02.29.56.311442@cfconsulting.ca>
References: <pan.2004.01.17.02.29.56.311442@cfconsulting.ca>
On Sat, Jan 17, 2004 at 02:30:01AM +0000, Colin Fox wrote:
> For each person in the people table, they may or may not have a record in
> a, may or may not have a record in b, and may or may not have a record in
> c.
...
> But I'd like to be able to do something like:
>
> select
> id, name, a.field1, b.field2, c.field3
> from
> people p left outer join a on a.person_id = p id,
> people p left outer join b on b.person_id = p.id,
> people p left outer join c on c.person_id = p.id;
You can just chain the joins and the Right Thing will happen:
SELECT id, name, a.field1, b.field2, c.field3
FROM
people p
LEFT OUTER JOIN a ON (p.id = a.person_id)
LEFT OUTER JOIN a ON (p.id = b.person_id)
LEFT OUTER JOIN a ON (p.id = c.person_id)
I'm not sure that this behaviour is mandated by the SQL standard;
a certain other popular open source database-like product interprets
the same construction differently. But it does do what you want in
postgres.
Richard
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: richard@ruthie.org
Subject: Re: Left joins with multiple tables
In-Reply-To: <20040118215714.GA11708@guests.deus.net>
* 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