Re: 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.
> -----------------------------------------
>