1

I'm working on the query bellow. Its structure is based on someone else's similar query...

SELECT  
    tptype.Code AS ThirdpartyTypeCode,
    ptf.Ticker AS TextId,
    finIn.InternalCode AS CompanyCode,

    TP.BrokerFocus,
    TP.EntryDate,
    TP.EndDate,
    TP.AccountingFundIdentifier,

    contacts1.FirstName AS Contact1FirstName,
    contacts1.LastName AS Contact1LastName,
    contacts1.Title AS Contact1Role,
    contacts2.FirstName AS Contact2FirstName,
    contacts2.LastName AS Contact2LastName,
    contacts2.Title AS Contact2Role,

    ParentThirdParty.TypeCode AS ParentTypeCode,
    ParentThirdParty.CompanyCode AS ParentCompanyCode

FROM dbo.ThirdParty AS TP
    LEFT JOIN dbo.ThirdPartyType as tptype on TP.ThirdPartyTypeId = tpType.Id
    LEFT JOIN dbo.Portfolio AS ptf ON TP.FundId = ptf.Id
    LEFT JOIN dbo.FinancialInstitution AS finIn ON TP.FinancialInstitutionId = finIn.Id

    LEFT JOIN (SELECT ThirdParty.Id, ThirdPartyType.Code AS TypeCode, FinancialInstitution.InternalCode AS CompanyCode
               FROM 
               dbo.ThirdPartyType AS ThirdPartyType INNER JOIN dbo.ThirdParty AS ThirdParty ON ThirdParty.ThirdPartyTypeId = ThirdPartyType.Id
               INNER JOIN dbo.FinancialInstitution AS FinancialInstitution ON ThirdParty.FinancialInstitutionId = FinancialInstitution.Id) AS ParentThirdParty
               ON TP.ParentThirdPartyId = ParentThirdParty.Id
    LEFT JOIN 
        (SELECT contact.Id, contact.PersonId, dbo.Person.FirstName, dbo.Person.LastName, contact.Title
         FROM (SELECT dbo.ThirdPartyContact.Id, dbo.ThirdPartyContact.Title, dbo.ThirdPartyContact.PersonId, ROW_NUMBER() OVER (ORDER BY dbo.ThirdPartyContact.Id) as rn
               FROM dbo.ThirdPartyContact
               INNER JOIN dbo.Person ON Person.Id = dbo.ThirdPartyContact.PersonId
               WHERE dbo.ThirdPartyContact.ThirdPartyId = TP.Id
               ) AS contact
         inner join dbo.Person ON Person.Id = contact.PersonId
         WHERE rn=1) AS contacts1
         ON 1 = 1
    OUTER APPLY 
        (SELECT contact.Id, contact.PersonId, dbo.Person.FirstName, dbo.Person.LastName, contact.Title
         FROM (SELECT dbo.ThirdPartyContact.Id, dbo.ThirdPartyContact.Title, dbo.ThirdPartyContact.PersonId, ROW_NUMBER() OVER (ORDER BY dbo.ThirdPartyContact.Id) as rn
               FROM dbo.ThirdPartyContact
               INNER JOIN dbo.Person ON Person.Id = dbo.ThirdPartyContact.PersonId
               WHERE dbo.ThirdPartyContact.ThirdPartyId = TP.Id
               ) AS contact
         inner join dbo.Person ON Person.Id = contact.PersonId
         WHERE rn=2) AS contacts2

As you can see, an APPLY is used to select the first line of results from the a sub-query. As I understand it, the APPLY was used here to replace LEFT JOIN, because there is no join condition, since the selection is only based on the row number.

I was wondering if I could rewrite this part for it not to use APPLY anymore, but a LEFT JOIN instead. As a first step, I wondered if I could leave the sub-query as it is, but write the LEFT JOIN with a condition that would always be true:

LEFT JOIN 
    (SELECT contact.Id, contact.PersonId, dbo.Person.FirstName, dbo.Person.LastName, contact.Title
     FROM (SELECT dbo.ThirdPartyContact.Id, dbo.ThirdPartyContact.Title, dbo.ThirdPartyContact.PersonId, ROW_NUMBER() OVER (ORDER BY dbo.ThirdPartyContact.Id) as rn
           FROM dbo.ThirdPartyContact
           INNER JOIN dbo.Person ON Person.Id = dbo.ThirdPartyContact.PersonId
           WHERE dbo.ThirdPartyContact.ThirdPartyId = TP.Id
           ) AS contact
     inner join dbo.Person ON Person.Id = contact.PersonId
     WHERE rn=1) AS contacts1
     ON 1 = 1

But I get the following error:

The multi-part identifier "TP.Id" could not be bound.

I'm not sure I understand why this doesn't work.

  • 1
    OUTER/CROSS APPLY allows the subquery to access columns on the "outside"; with a LEFT/RIGHT/INNER JOIN, you can only correlate using the ON predicate. – Jon Seigel Apr 18 '14 at 16:25
  • The alias may not be able to traverse the scope to the sub query, try and put the alias "TP" on the sub query? – RateControl Apr 18 '14 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.