Re: [SQL] joins and stuff

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