Received: from jacqro.COM (dsl.jacqro.com [166.84.147.137] (may be forged)) by hub.org (8.10.1/8.10.1) with ESMTP id e6CHavW80040 for ; Wed, 12 Jul 2000 13:36:58 -0400 (EDT) Received: (from jacques@localhost) by jacqro.COM (8.9.3/8.9.3) id KAA02584; Wed, 12 Jul 2000 10:41:12 -0400 Date: Wed, 12 Jul 2000 10:41:12 -0400 From: Jacques Williams To: Gary Stainburn Cc: pgsql-sql@postgresql.org Subject: Re: join if there, blank if not Message-ID: <20000712104112.A2531@jacqro3.jacqro.com> References: <01BFEC1E.FF0A43A0@garys.ringways.co.uk> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 1.0pre3us In-Reply-To: <01BFEC1E.FF0A43A0@garys.ringways.co.uk> X-Archive-Number: 200007/135 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. > ----------------------------------------- >