title: SQL Server - get rid of derived query in join
body: <p>I'm interested in some suggestions with examples on how to 1)get rid of the derived query in the left outer join and 2)remove/consolidate the cte  cteSecType in the code below The derived query in the join chooses the minimum assistant_personnel_number out of a list; there can be multiple primary and  backup assistants associated with an employee . thx</p>
  
  <pre><code>WITH    cteAssistants ( executive_personnel_number, assistant_personnel_number, assistant_type, assign_role )
            AS ( SELECT   CASE WHEN ISNUMERIC(JP.XA_asgn_emplid) = 1
                               THEN JP.XA_asgn_emplid
                               ELSE ''
                          END AS executive_personnel_number ,
                          JAP.emplid AS assistant_personnel_number ,
                          LAT1.FIELDVALUE AS assistant_type ,
                          LAT3.xlatshortname AS assign_role
                 FROM     dbo.XA_ASGN_PRNT AS KAP
                          LEFT OUTER JOIN dbo.XA_ASSIGNMENTS AS KA ON JP.emplid = JAP.emplid
                                                            AND JP.effdt = JAP.effdt
                          LEFT OUTER JOIN dbo.XA_EMPLOYEES AS EXECT ON EXECT.EMPLID = JP.XA_ASGN_EMPLID
                          LEFT OUTER JOIN dbo.XA_EMPLOYEES AS ASST ON ASST.EMPLID = JAP.EMPLID
                          LEFT OUTER JOIN dbo.XLATITEM AS XLAT1 ON LAT1.fieldname = 'XA_ASGN_TYPE'
                                                            AND LAT1.fieldvalue = JAP.XA_asgn_type
                          LEFT OUTER JOIN dbo.XLATITEM AS XLAT3 ON LAT3.fieldname = 'XA_ASGN_ROLE'
                                                            AND LAT3.fieldvalue = JP.XA_asgn_role
                 WHERE    JAP.effdt = ( SELECT    MAX(effdt)
                                        FROM      dbo.XA_ASGN_PRNT
                                        WHERE     emplid = JAP.emplid
                                                  AND effdt &lt;= GETDATE()
                                      )
                          --Return data only when both executive and assistant are still active; null is for Floaters
                          AND ( EXECT.HR_STATUS = 'A'
                                OR EXECT.HR_STATUS IS NULL
                              )
                          AND ASST.HR_STATUS = 'A'
  
                          AND ( JAP.XA_asgn_type = 'F'
  
                                OR ( JAP.XA_asgn_type IN ( 'A', 'AF' )
                                     AND JP.XA_asgn_person = 'Y'
                                   )
                              )
               ),
          cteSecType ( assistant_personnel_number, SecType )
            AS ( SELECT   assistant_personnel_number ,
                          assistant_type AS SecType
                 FROM     cteAssistants 
                 GROUP BY assistant_type ,
                          assistant_personnel_number
               )
      SELECT  EMP.XA_NETWORK_ID AS network_id ,
              EMP.XA_EMPLID AS empid ,
              EMP.XA_EMPLID AS employeeNumber ,
              EMP.XA_FIRST_NAME AS first_name ,
              EMP.XA_LAST_NAME AS last_name ,
              SECRES.SecType AS SecType ,
              AsstInfo.XA_fml_pref_name AS PrimaryAssistant_FML ,
              AsstInfo.XA_network_id AS PrimaryAssistant_Newtork_ID ,
              AsstInfo.XA_EMPLID AS PrimaryAssistant_EmpID ,
              AsstInfo.XA_phone_ext AS PrimaryAssitant_Extension
      FROM    dbo.XA_EMPLOYEES AS EMP
              LEFT OUTER JOIN dbo.XA_EMPLOYEES AS MGR ON EMP.supervisor_id = MGR.emplid
              LEFT OUTER JOIN cteSecType AS SECRES ON CAST(CAST(SECRES.assistant_personnel_number AS INT) AS VARCHAR(11)) = EMP.XA_EMPLID
              LEFT OUTER JOIN ( SELECT    executive_personnel_number AS AttorneyID ,
                                          MIN(assistant_personnel_number) AS AssistantID
                                FROM      cteEmployeeAssistants AS A
                                WHERE     ( assign_role = 'Primary' )
                                GROUP BY  executive_personnel_number
                              ) AS ASST ON ASST.AttorneyID = EMP.XA_EMPLID
              LEFT OUTER JOIN dbo.XA_EMPLOYEES AS ASSTINFO ON ASSTINFO.XA_EMPLID = ASST.AssistantID
      WHERE   ( EMP.HR_STATUS = 'A' )
              AND ( EMP.PER_ORG IN ( 'EMP', 'CWR' ) )
              AND ( ISNULL(EMP.XA_NETWORK_ID, '') &lt;&gt; '' )   
  </code></pre>
  
  <p>GO</p>