From: Henry Lafleur <HLafleur@phoenixforge.com>
To: Jacques Williams <jacques@jacqro.COM>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: RE: join if there, blank if not
Date: Wed, 12 Jul 2000 14:51:24 -0400
Message-ID: <E332B20358CDD1118D7A00A0C995F75A91441D@XSERVER> (raw)
Jacques,
The problem with using the union in this way is that you get NULLs for a
number weather or not it has an associated record in calls.
To do a pure outer join, it would be something like this:
select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
from calls c, numbers n
where c.cnumber=n.nnumber
union all
select null as cdate, null as cextn, null as cnumber, nnumber, ndesc
from numbers
WHERE nnumber NOT IN (SELECT nnumber FROM calls);
---
What I have always had trouble with, though, is if you have multiple fields
for a primary key. For example, if a customer master table also had ship-to
locations as the key and you wanted to get all customers and any orders for
that customer, in rough ANSI SQL it would be:
SELECT c.cust_number, c.ship_to, o.item
FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND
c.ship_to = o.ship_to
then, in the union, it is not clear how to do it:
SELECT c.cust_number, c.ship_to, o.item
FROM cust c, orders o
WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to
UNION
SELECT cust_number, ship_to, NULL AS item
FROM cust
WHERE ???
which I never know what to do at ??? -
WHERE c.cust_number NOT IN (SELECT cust_number FROM orders)
is one choice, but this doesn't help if the ship to doesn't match. We can
get wild and try -
WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE
ship_to = cust.ship_to)
but if you go to two and three keys, what happens then? It seems like it
should work if we continue. But how efficiently does this work?
Has anyone examined this problem?
Thanks,
Henry
-----Original Message-----
From: Jacques Williams [mailto:jacques@jacqro.COM]
Sent: Wednesday, July 12, 2000 9:41 AM
To: Gary Stainburn
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] join if there, blank if not
Gary,
What you want here is an outer join. The syntax would look something like
this:
select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
from calls c, numbers n
where c.cnumber=n.nnumber
union all
select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc
from numbers;
(I haven't tried this, but it should work.) For more information on outer
joins, see Bruce Momjian's book at
http://www.postgresql.org/docs/aw_pgsql_book/ .
Jacques Williams
On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote:
> Hi all,
>
> I just can't get my head round this one so I hope one of you can.
>
> I've got two tables, one holding phone calls, and another holding phone
numbers.
>
> I want to do a select where if the number in the calls table exists
> in the numbers table, the description is included otherwise the
> description field is blank.
>
> Unfortunately, using the select I've tried, if the number is not on
> the one of the tables, it's ignored.
>
> Calls table
> cdate date
> ctime time
> cextn char(3)
> cnumber x(12)
>
> Numbers table
>
> nnumber x(12)
> ndesc x(30)
>
> Select I tried.
>
> select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc
> from calls c, numbers n where c.cnumber = n.nnumber;
>
> -----------------------------------------
> Gary Stainburn.
> Work: http://www.ringways.co.uk mailto:gary.stainburn@ringways.co.uk
> REVCOM: http://www.revcom.org.uk mailto:gary.stainburn@revcom.org.uk
> -----------------------------------------
> Murphy's Laws: (327) The minute before the engineer arrives, the printer
starts working.
> -----------------------------------------
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: HLafleur@phoenixforge.com, jacques@jacqro.COM, gary.stainburn@ringways.co.uk
Subject: Re: join if there, blank if not
In-Reply-To: <E332B20358CDD1118D7A00A0C995F75A91441D@XSERVER>
References: <E332B20358CDD1118D7A00A0C995F75A91441D@XSERVER>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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