subquery - at most one record can be returned

[WebDude]

i'm back.

I have a table "SocialWorkerTable" with a field "fkRequestID" that is linked
to the primary key field "pkRequestID" in the table "Requests". The
relationship is such that, every record in the "SocialWorkerTable" must have
*one and only one* record in the "Requests" table. And, every record in the
"Requests" table can have several related records in the "SocialWorkerTable"
table.

Using the query window/grid, I wrote a simple "QueryA" which displays
records from the "Requests" and this field...

SWName: (SELECT [SocialWorkerName] FROM SocialWorkerTable WHERE
SocialWorkerTable.Date = (SELECT Max(T.Date) FROM SocialWorkerTable As T
WHERE T.[fkRequestID] = SocialWorkerTable.[fkRequestID] AND
Requests.pkRequestID = SocialWorkerTable.fkRequestID))


However, if a "Request" record listed in "QueryA" does not have a related
record in the SocialWorkerTable, then "QueryA" returns no results and i get
the following error on my subquery..."At most one record can be returned by
this SubQuery"

Can i tweak my subquery to take into consideration that there may not be a
related record in the the "SocialWorkerTable" and to please return a zero
length empty string instead of bombing?

Any help, links, pointers or examples, would be appreciated

Cheers,
WebDude 2006


ps: i tried adding nz(), IIF() and IsNull to the subquery, but perhaps i
didnt put them in the right spot?

 

[Tom Ellison]

Dear Dude:

One problem would be that there may be more than one row in
SocialWorkerTable that has the same date. Then, the outer subquery will try
to return several SocialWorkerName. Even if the two records with the same
date have the same SocialWorkerName, you have more than one row, and you get
this error.

As a test to see if this is what is causing the problem, try this small
change:

SWName: (SELECT MIN([SocialWorkerName]) FROM SocialWorkerTable WHERE
SocialWorkerTable.Date = (SELECT Max(T.Date) FROM SocialWorkerTable As T
WHERE T.[fkRequestID] = SocialWorkerTable.[fkRequestID] AND
Requests.pkRequestID = SocialWorkerTable.fkRequestID))

That will at least ensure you never have multiple rows from the outer
subquery. Of course, this is not necessarily the results you want to see.
But, given this inconsistency in your logic, it is not possible for me to
surmise what that might be. So, the above is a suggested test, not
necessarily a suggested solution.

If there is no row with that date, then it will return null. You could test
for and change nulls to something else, but either way, I don't know that
this is the real cause.

Another way to test would be to add a new column:

TestMe: (SELECT COUNT(*) FROM SocialWorkerTable WHERE
SocialWorkerTable.Date = (SELECT Max(T.Date) FROM SocialWorkerTable As T
WHERE T.[fkRequestID] = SocialWorkerTable.[fkRequestID] AND
Requests.pkRequestID = SocialWorkerTable.fkRequestID))

When this is 0 there were none. When it is 1, everything should be OK.
When it is > 1, you have trouble.

Tom Ellison

i'm back.

I have a table "SocialWorkerTable" with a field "fkRequestID" that is
linked
to the primary key field "pkRequestID" in the table "Requests". The
relationship is such that, every record in the "SocialWorkerTable" must
have
*one and only one* record in the "Requests" table. And, every record in
the
"Requests" table can have several related records in the
"SocialWorkerTable"
table.

Using the query window/grid, I wrote a simple "QueryA" which displays
records from the "Requests" and this field...

SWName: (SELECT [SocialWorkerName] FROM SocialWorkerTable WHERE
SocialWorkerTable.Date = (SELECT Max(T.Date) FROM SocialWorkerTable As T
WHERE T.[fkRequestID] = SocialWorkerTable.[fkRequestID] AND
Requests.pkRequestID = SocialWorkerTable.fkRequestID))


However, if a "Request" record listed in "QueryA" does not have a related
record in the SocialWorkerTable, then "QueryA" returns no results and i
get
the following error on my subquery..."At most one record can be returned
by
this SubQuery"

Can i tweak my subquery to take into consideration that there may not be a
related record in the the "SocialWorkerTable" and to please return a zero
length empty string instead of bombing?

Any help, links, pointers or examples, would be appreciated

Cheers,
WebDude 2006


ps: i tried adding nz(), IIF() and IsNull to the subquery, but perhaps i
didnt put them in the right spot?

 

[WebDude]

Dear Tom,

You were quiet right about the flaw in my logic. Indeed, whenever the inner
subquery returned more then one record, everything bombed. So, i took your
suggestion and modified the Outer SubQuery...

SWName: (SELECT MAX([SocialWorkerName]) FROM SocialWorkerTable WHERE
SocialWorkerTable.Date = (SELECT Max(T.Date) FROM SocialWorkerTable As T
WHERE T.[fkRequestID] = SocialWorkerTable.[fkRequestID] AND
Requests.pkRequestID = SocialWorkerTable.fkRequestID))


Thanks again!


Um,
Would you happen to know how i might return the
SocialWorkerTable.[SocialWorkerTelephone] field from the SAME RECORD which
the SocialWorkerTable.[SocialWorkerName] came from in the above subquery? I
wrote the following which seems to work...


SWTel: (SELECT [SocialWorkerTelephone] FROM SocialWorkerTable WHERE
SocialWorkerTable.[SocialWorkerName] = (SELECT MAX([SocialWorkerName]) FROM
SocialWorkerTable WHERE SocialWorkerTable.Date = (SELECT Max(T.Date) FROM
SocialWorkerTable As T WHERE T.[fkRequestID] =
SocialWorkerTable.[fkRequestID] AND Requests.pkRequestID =
SocialWorkerTable.fkRequestID)) AND SocialWorkerTable.Date = (SELECT
Max(Temp.Date) FROM SocialWorkerTable As Temp WHERE Temp.[fkRequestID] =
SocialWorkerTable.[fkRequestID] AND Requests.pkRequestID =
SocialWorkerTable.fkRequestID))


The code above returns the right SocialWorkerTelephone number for each
SocialWorkerTable record retrieved by the inner subqueries. However, the code
seems a bit complex so i was wondering if there was an easier way to write it?


Any help,
direction,
links or
suggestions would be appreciated

Cheers,
Vote WebDude in 2006








PS: maybe if i wrote SWName as a function, then called it in SWTel i could
then reduce the SWTel code somewhat?

SWTel: (SELECT [SocialWorkerTelephone] FROM SocialWorkerTable WHERE
SocialWorkerTable.[SocialWorkerName] = SWNameFunction(pkRequestId) AND
SocialWorkerTable.Date = (SELECT Max(Temp.Date) FROM SocialWorkerTable As
Temp WHERE Temp.[fkRequestID] = SocialWorkerTable.[fkRequestID] AND
Requests.pkRequestID = SocialWorkerTable.fkRequestID))