Unexpected behavior when filtering Archived requests on Account

Unexpected behavior when filtering Archived requests on Account

What we want to do: Filter archived requests on <Account name>.
Expected behavior: List all archived requests with <Account name>.
Actual behavior: List all archived requests that has a requester where the requester is in account with <Account name>
Result: Some of the archived requests that are in fact connected to the account are filtered out because the requester cannot be connected to an account (maybe the requester is deleted or something).


We have two different methods for connecting accounts in archived requests vs non archived requests. I would argue that the method used in non archived requests is correct (see queries below). I would expect the archived requests to use the tables Arc_WorkOrderAccountMapping and AccountDefinition to find the correct account for a request. I would not expect any joins to be made on Requester, but if there are then it should not be done the way it is done since it limits the results because of the INNER JOINs.


SQL query Archived requests:

  1. SELECT
  2. "arcwo"."WORKORDERID" AS "Request ID"
  3. , "ad"."ORG_NAME" AS "Account"
  4. , "arcwo"."RESOLVEDTIME" AS "Resolved Time" 
  5. FROM "Arc_WorkOrder" "arcwo"
  6. INNER JOIN "UserDepartment" "ud" ON "arcwo"."REQUESTERID"="ud"."USERID"
  7. INNER JOIN "DepartmentDefinition" "ddef" ON "ud"."DEPTID"="ddef"."DEPTID"
  8. INNER JOIN "AccountSiteMapping" "asm" ON "ddef"."SITEID"="asm"."SITEID"
  9. INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID"
  10. INNER JOIN "UserDepartment" ON "arcwo"."REQUESTERID"="UserDepartment"."USERID"
  11. INNER JOIN "DepartmentDefinition" ON "UserDepartment"."DEPTID"="DepartmentDefinition"."DEPTID"
  12. WHERE  ( ( "ad"."ORG_NAME" LIKE N'%Account name%' )
  13. ...


SQL Query requests (not archived):
  1. SELECT
  2. "wo"."WORKORDERID" AS "Request ID"
  3. , "ad"."ORG_NAME" AS "Account"
  4. , "wo"."RESOLVEDTIME" AS "Resolved Time" 
  5. FROM "WorkOrder" "wo"
  6. LEFT JOIN "AccountSiteMapping" "asm" ON "wo"."SITEID"="asm"."SITEID"
  7. LEFT JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID"
  8. INNER JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
  9. WHERE  ( ( "ad"."ORG_NAME" = N'Bodens Kommun' )
  10. ...