pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLeft joins with multiple tables
3+ messages / 3 participants
[nested] [flat]
* Left joins with multiple tables
@ 2004-01-17 02:30 Colin Fox <cfox@cfconsulting.ca>
0 siblings, 2 replies; 3+ messages in thread
From: Colin Fox @ 2004-01-17 02:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Left joins with multiple tables
@ 2004-01-18 21:57 Richard Poole <richard@ruthie.org>
parent: Colin Fox <cfox@cfconsulting.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Poole @ 2004-01-18 21:57 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Left joins with multiple tables
@ 2004-01-19 04:58 Denis <sqllist@coralindia.com>
parent: Colin Fox <cfox@cfconsulting.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Denis @ 2004-01-19 04:58 UTC (permalink / raw)
To: pgsql-sql
Hi Colin,
Try
select id, name, a.field1, b.field2, c.field3
from
people 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-19 04:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-17 02:30 Left joins with multiple tables Colin Fox <cfox@cfconsulting.ca>
2004-01-18 21:57 ` Richard Poole <richard@ruthie.org>
2004-01-19 04:58 ` Denis <sqllist@coralindia.com>
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