c# - SQL function returns entries multiple times -

i using sql function return entries, in 2 tables. table contains information userid, fileid , created datetime. table b contains information userid, fileid , transmission state.

the function should return entries of userid, fileid, created , state

the function looks this:

with findnewestversion     (         select created cdate, fileid fid, userid uid, row_number() on (partition fileid, userid order created desc)rn tablea         created <= @duedate     )      select * tableb b, tablea      inner join (select cdate, userid, fileid findnewestversion rn = 1) x     on a.created = cdate , a.userid = uid , a.fileid = fid       a.userid = isnull(@userid, a.userid) , a.fileid = isnull(@fileid, q.fileid)     , b.tasktype = 'transmission' 

and call of function using in c# looks this:

select a.userid, a.fileid, a.created, a.versionid,  case when b.variablecolumn1 null null else b.variable1 end transstate  dbo.lsfn_getmatrixatduedate([parameters]) left join tableb b  on a.versionid = b.variablecolumn2 , a.versionid = b.variablecolumn3 

i using call, because columns in table b can contain different information various tasks , have determine correct column of config file "transmission task". don't wanted use dynamic sql, can use columnnames parameters within function itself.

so, function , call working correctly. let's example.

table contains:

userid   | fileid   | created -------------------------------- usera    | filea    | 01.01.2011 usera    | fileb    | 01.01.2011 userc    | filec    | 01.01.2011 

table b contains:

variable1 (state)  | variable2 (version/file) | variable3 (userid)  ------------------------------------------------------------------ completed          | filea                    | usera completed          | filec                    | userc 

so, lets say, want entries of both tables, belong usera result should this:

userid | fileid | created    | state ---------------------------------------- usera  | filea  | 01.01.2011 | completed usera  | fileb  | 01.01.2011 | null 

however, getting multple entries each results, result looks this:

userid | fileid | created    | state ---------------------------------------- usera  | filea  | 01.01.2011 | completed usera  | filea  | 01.01.2011 | completed usera  | fileb  | 01.01.2011 | null usera  | fileb  | 01.01.2011 | null 

it somehow related amount of entries in table b. if delete entry of userc, result entries correct, if add row tableb (so there 3 entries), each result 3 times. guess has select, calling function, don't know wrong there...