pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOptional join
5+ messages / 5 participants
[nested] [flat]
* Optional join
@ 2003-08-14 14:40 Slawek Jarosz <Jarosz.S@ems-t.ca>
0 siblings, 4 replies; 5+ messages in thread
From: Slawek Jarosz @ 2003-08-14 14:40 UTC (permalink / raw)
To: pgsql-sql
Hi,
I trying to write a query that will join 2 tables. Here's the concept:
Table 1: table1, primary key pk1
Table 2: table2, primary key pk2
One of the fields (f2) in table2 contains either the primary key of table1 or a NULL value. So normally a pretty basic query:
SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 = table1.pk1;
BUT what I would like to do is show all records of Table 1 even if there is no match in Table 2. Meaning that the reults could be
table1... table2.pk2
table1... NULL
Doable?
Thanks.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optional join
@ 2003-08-15 04:40 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Slawek Jarosz <Jarosz.S@ems-t.ca>
3 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-08-15 04:40 UTC (permalink / raw)
To: Slawek Jarosz <Jarosz.S@ems-t.ca>; +Cc: pgsql-sql
On Thu, 14 Aug 2003, Slawek Jarosz wrote:
> Hi,
>
> I trying to write a query that will join 2 tables. Here's the concept:
> Table 1: table1, primary key pk1 Table 2: table2, primary key pk2
>
> One of the fields (f2) in table2 contains either the primary key of
> table1 or a NULL value. So normally a pretty basic query:
>
> SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 =
> table1.pk1;
>
> BUT what I would like to do is show all records of Table 1 even if
> there is no match in Table 2. Meaning that the reults could be
> table1... table2.pk2
> table1... NULL
See outer joins.
Something like:
SELECT table1.*,table2.pk FROM
table1 LEFT OUTER JOIN table on (table2.f2=table1.pk1);
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optional join
@ 2003-08-15 04:57 Bruno Wolff III <bruno@wolff.to>
parent: Slawek Jarosz <Jarosz.S@ems-t.ca>
3 siblings, 0 replies; 5+ messages in thread
From: Bruno Wolff III @ 2003-08-15 04:57 UTC (permalink / raw)
To: Slawek Jarosz <Jarosz.S@ems-t.ca>; +Cc: pgsql-sql
On Thu, Aug 14, 2003 at 10:40:02 -0400,
Slawek Jarosz <Jarosz.S@ems-t.ca> wrote:
> Hi,
>
> I trying to write a query that will join 2 tables. Here's the concept:
> Table 1: table1, primary key pk1
> Table 2: table2, primary key pk2
>
> One of the fields (f2) in table2 contains either the primary key of table1 or a NULL value. So normally a pretty basic query:
>
> SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 = table1.pk1;
>
> BUT what I would like to do is show all records of Table 1 even if there is no match in Table 2. Meaning that the reults could be
> table1... table2.pk2
> table1... NULL
>
> Doable?
You use outer joins to do this. Something like:
SELECT table1.*, table2.pk2 FROM
table1 left join table2 on table2.f2 = table1.pk1;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optional join
@ 2003-08-15 05:50 David Fetter <shackle@fetter.org>
parent: Slawek Jarosz <Jarosz.S@ems-t.ca>
3 siblings, 0 replies; 5+ messages in thread
From: David Fetter @ 2003-08-15 05:50 UTC (permalink / raw)
To: pgsql-sql
"Slawek Jarosz" <Jarosz.S@ems-t.ca> wrote:
> Hi,
> I trying to write a query that will join 2 tables. Here's the
> concept:
> Table 1: table1, primary key pk1
> Table 2: table2, primary key pk2
> One of the fields (f2) in table2 contains either the primary key of
> table1 or a NULL value. So normally a pretty basic query:
> SELECT table1.*, table2.pk2
> FROM table1, table2 WHERE table2.f2 = table1.pk1;
> BUT what I would like to do is show all records of Table 1 even if
> there is no match in Table 2. Meaning that the reults could be
> table1... table2.pk2
> table1... NULL
> Doable?
Yes.
SELECT table1.*, table2.pk2
FROM table1 LEFT OUTER JOIN table2 ON (table1.pk1 = table2.pk2);
HTH :)
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778
Power over a man's subsistence is power over his will.
Alexander Hamilton
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optional join
@ 2003-08-16 16:56 Ian Barwick <barwick@gmx.net>
parent: Slawek Jarosz <Jarosz.S@ems-t.ca>
3 siblings, 0 replies; 5+ messages in thread
From: Ian Barwick @ 2003-08-16 16:56 UTC (permalink / raw)
To: Slawek Jarosz <Jarosz.S@ems-t.ca>; pgsql-sql
On Thursday 14 August 2003 16:40, Slawek Jarosz wrote:
> Hi,
>
> I trying to write a query that will join 2 tables. Here's the concept:
> Table 1: table1, primary key pk1
> Table 2: table2, primary key pk2
>
> One of the fields (f2) in table2 contains either the primary key of table1
> or a NULL value. So normally a pretty basic query:
>
> SELECT table1.*, table2.pk2 FROM table1, table2 WHERE table2.f2 =
> table1.pk1;
>
> BUT what I would like to do is show all records of Table 1 even if there is
> no match in Table 2. Meaning that the reults could be table1...
> table2.pk2
> table1... NULL
>
> Doable?
You need an OUTER JOIN, see e.g.
http://www.postgresql.org/docs/7.3/static/tutorial-join.html
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-08-16 16:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-14 14:40 Optional join Slawek Jarosz <Jarosz.S@ems-t.ca>
2003-08-15 04:40 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-15 04:57 ` Bruno Wolff III <bruno@wolff.to>
2003-08-15 05:50 ` David Fetter <shackle@fetter.org>
2003-08-16 16:56 ` Ian Barwick <barwick@gmx.net>
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