From: Colin Fox <cfox@cfconsulting.ca>
Organization: CF Consulting Inc.
Subject: Left joins with multiple tables
User-Agent: Pan/0.14.0 (I'm Being Nibbled to Death by Cats!)
Message-Id: <pan.2004.01.17.02.29.56.311442@cfconsulting.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 8bit
Lines: 30
Date: Sat, 17 Jan 2004 02:30:01 GMT
To: pgsql-sql@postgresql.org

Hi, all.

I've got a bit of a problem here. I have 4 tables - people, a, b, c (not
the original names).

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.

Handling the first table (a) is easy:

select id, name
from people p left outer join a on a.person_id = p id;

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;

Naturally you can't repeat the 'people p' clause 3 times, but is there
some other syntax that would let me do this?

Thanks!
  cf