pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: 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 10:41:12 -0400
Message-ID: <20000712104112.A2531@jacqro3.jacqro.com> (raw)
In-Reply-To: <01BFEC1E.FF0A43A0@garys.ringways.co.uk>
References: <01BFEC1E.FF0A43A0@garys.ringways.co.uk>

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



view thread (5+ messages)  latest in thread

Message-ID: <20000712104112.A2531@jacqro3.jacqro.com>
Permalink:  ../20000712104112.A2531@jacqro3.jacqro.com/
Also on:    postgresql.org/message-id/20000712104112.A2531@jacqro3.jacqro.com

 · 

reply

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: jacques@jacqro.COM, gary.stainburn@ringways.co.uk
  Subject: Re: join if there, blank if not
  In-Reply-To: <20000712104112.A2531@jacqro3.jacqro.com>
  References: <01BFEC1E.FF0A43A0@garys.ringways.co.uk> <20000712104112.A2531@jacqro3.jacqro.com>

* 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