RE: join if there, blank if not

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(at)jacqro(dot)COM]
Sent: Wednesday, July 12, 2000 9:41 AM
To: Gary Stainburn
Cc: pgsql-sql(at)postgresql(dot)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(dot)stainburn(at)ringways(dot)co(dot)uk
> REVCOM: http://www.revcom.org.uk mailto:gary(dot)stainburn(at)revcom(dot)org(dot)uk
> -----------------------------------------
> Murphy's Laws: (327) The minute before the engineer arrives, the printer
starts working.
> -----------------------------------------
>