Hi,

I have this query that (stripped right down) goes something like this :

SELECT
    [Person_PrimaryContact].[LegalName],
    [Person_Manager].[LegalName],
    [Person_Owner].[LegalName],
    [Person_ProspectOwner].[LegalName],
    [Person_ProspectBDM].[LegalName],
    [Person_ProspectFE].[LegalName],
    [Person_Signatory].[LegalName] 

FROM
  [Cache]
    LEFT JOIN [dbo].[Person] AS [Person_Owner] WITH (NOLOCK) ON [Person_Owner].[PersonID] = [Cache].[ClientOwnerID]
    LEFT JOIN [dbo].[Person] AS [Person_Manager] WITH (NOLOCK) ON [Person_Manager].[PersonID] = [Cache].[ClientManagerID]
    LEFT JOIN [dbo].[Person] AS [Person_Signatory] WITH (NOLOCK) ON [Person_Signatory].[PersonID] = [Cache].[ClientSignatoryID]
    LEFT JOIN [dbo].[Person] AS [Person_PrimaryContact] WITH (NOLOCK) ON [Person_PrimaryContact].[PersonID] = [Cache].[PrimaryContactID]
    LEFT JOIN [dbo].[Person] AS [Person_ProspectOwner] WITH (NOLOCK) ON [Person_ProspectOwner].[PersonID] = [Cache].[ProspectOwnerID]
    LEFT JOIN [dbo].[Person] AS [Person_ProspectBDM] WITH (NOLOCK) ON [Person_ProspectBDM].[PersonID] = [Cache].[ProspectBDMID]
    LEFT JOIN [dbo].[Person] AS [Person_ProspectFE] WITH (NOLOCK) ON [Person_ProspectFE].[PersonID] = [Cache].[ProspectFEID]

Person is a huge table and each join to it has a pretty significant hit in the execution plan.

Is there anyway I can adjust this query so that I am only linking to it once, or at least get SQL Server to scan through it only once?