Received: from nest.bistbn.com (postgres@nest.bistbn.com [209.88.174.52]) by hub.org (8.9.1/8.9.1) with ESMTP id DAA09003 for ; Wed, 2 Dec 1998 03:52:37 -0500 (EST) (envelope-from postgres@nest.bistbn.com) Received: from localhost (postgres@localhost) by nest.bistbn.com (8.9.1/8.9.1) with SMTP id KAA13633; Wed, 2 Dec 1998 10:52:21 +0200 (IST) Date: Wed, 2 Dec 1998 10:52:21 +0200 (IST) From: Postgres DBA To: Walt Bigelow cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] joins and stuff In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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 > > > >