Unanswered: Inner join in user-defined function
can anyone please help with this little issue:
I have a user-defined function that works on two tables. First has more records than the second and therefore i am trying to use an inner join to limit the number of records to search by. However, the function does not seem to understand this and for all records in first table which do not have corresponding records in the second table returns null in @cName variable.
Here's the function:
Where linkid is the field that records in both tables are corresponding by.
CREATE FUNCTION [dbo].[RS_NoName] (@cID char(9))
RETURNS bit AS
declare @Res as bit, @cName as char(50)
select @Res = null
select @cName = f.name
from tblFirst f (nolock) inner join tblSecond s (nolock)
where f.ID = @cID
if @cName is null or len(ltrim(rtrim(@cName)))=0
Therefore instead of getting a list of customers where tblFirst.name is null, i also get all customers which do not have corresponding records in tblSecond, regardless of their rblFirst.name value...
Can anyone please help?
Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.