Thread

1. joins and stuff

   Walt Bigelow <walt@stimpy.com> — 1998-12-02T04:38:58Z

   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
   
   
   
   

2. Re: [SQL] joins and stuff

   Postgres DBA <postgres@nest.bistbn.com> — 1998-12-02T08:52:21Z

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