Skip site navigation (1) Skip section navigation (2)

Re: Left joins with multiple tables

From: Richard Poole <richard(at)ruthie(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Left joins with multiple tables
Date: 2004-01-18 21:57:14
Message-ID: 20040118215714.GA11708@guests.deus.net (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: Vishal Kashyap @ [Sai Hertz And Control Systems]Date: 2004-01-18 22:29:00
Subject: Re: Trigger to identify which column(s) updated
Previous:From: Chris TraversDate: 2004-01-18 13:02:43
Subject: Re: Is it possible in PostgreSQL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group