Re: [SQL] joins and stuff

Поиск
Список
Период
Сортировка
Искать

Re: [SQL] joins and stuff

От:
Postgres DBA <postgres@nest.bistbn.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
> 
> 
> 
>

joins and stuff

От:
Walt Bigelow <walt@stimpy.com>
Дата:
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
FAQ