pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Postgres DBA <postgres@nest.bistbn.com>
To: Walt Bigelow <walt@stimpy.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] joins and stuff
Date: Wed, 2 Dec 1998 10:52:21 +0200 (IST)
Message-ID: <Pine.BSF.4.02.9812021041370.9370-100000@nest.bistbn.com> (raw)
In-Reply-To: <Pine.LNX.4.02.9812012015070.1331-100000@alice.stimpy.com>

hi!
I'd recommended to solve this problem manually and in the future
consider using transaction mechanism to prevent such half-updates...

On Tue, 1 Dec 1998, Walt Bigelow wrote:

> Hello all:
> 
> I am having an issue related to returning of records within a join.  Here
> is the join that I am doing:
> 
> SELECT 
> 	projecttitle, 
> 	wonumber, 
> 	date, 
> 	tbladdressdirectory.company as fclient,
> 	tbladdressdirectory_1.company as agency,
> 	tbladdressdirectory_2.company as agencyclient, 
> 	tblcontacts.contactname as contact 
> FROM 
> 	tblproject,
> 	tbladdressdirectory, 
> 	tbladdressdirectory as tbladdressdirectory_1, 
> 	tbladdressdirectory as tbladdressdirectory_2,
> 	tblcontacts 
> WHERE 
> 	tblproject.fclient = tbladdressdirectory.agencyid AND
> 	tblproject.agencyid = tbladdressdirectory_1.agencyid AND
> 	tblproject.aclientid = tbladdressdirectory_2.agencyid AND
> 	tblproject.fclientcontactid = tblcontacts.contactid AND
> 	projectid = 77;
> 
> I came upon a record with an invalid contactid in tblprojects (an id that
> is not in the tblcontacts table) and the resultant output is (0 rows). 
> 
> My question is how can I have the server return a row weather or not a
> join completed successfully?  i.e. Have it return a NULL or something when
> it cannot match the 2 tables, instead of failing completely.
> 
> It was mostly my stupid mistake that caused the above to occour -- the
> front end (msAccess  yuck) that I wrote failed to update or check to see
> if the contact I was deleting is actually in use or not.  But on another
> table I would like the option of having a field in the main table NULL and
> still have the select return everything else.
> 
> Thanks for any help,
> Walt
> 
> 
> 
> 




view thread (2+ messages)

Message-ID: <Pine.BSF.4.02.9812021041370.9370-100000@nest.bistbn.com>
Permalink:  ../Pine.BSF.4.02.9812021041370.9370-100000@nest.bistbn.com/
Also on:    postgresql.org/message-id/Pine.BSF.4.02.9812021041370.9370-100000@nest.bistbn.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: postgres@nest.bistbn.com, walt@stimpy.com
  Subject: Re: [SQL] joins and stuff
  In-Reply-To: <Pine.BSF.4.02.9812021041370.9370-100000@nest.bistbn.com>
  References: <Pine.LNX.4.02.9812012015070.1331-100000@alice.stimpy.com> <Pine.BSF.4.02.9812021041370.9370-100000@nest.bistbn.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