Thread: Left joins with multiple tables

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
leftouter 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

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

Hi Colin,

Try

select id, name, a.field1, b.field2, c.field3
frompeople p left outer join a on (a.person_id = p id)             left outer join b on (b.person_id = p.id)
left outer join c on (c.person_id = p.id);
 

HTH

Denis


----- Original Message ----- 
From: "Colin Fox" <cfox@cfconsulting.ca>
To: <pgsql-sql@postgresql.org>
Sent: Saturday, January 17, 2004 8:00 AM
Subject: [SQL] Left joins with multiple tables


> 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
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster