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