Forum Discussion

cpatte7372's avatar
cpatte7372
Copper Contributor
Apr 05, 2024
Solved

How to Modify JOINs to Show Data in Fields

Hello Community, The following SQL Code will produce the following output:   I would very much appreciate help modifying the code to show the data in fields, BizName and BizLastName. Data d...
  • Patooworld's avatar
    Apr 05, 2024
    SELECT
    NameDetails.FirstName,
    NameDetails.Surname,
    UserAccount.FirstName AS BizFirstName,
    UserAccount.LastName AS BizLastName
    FROM CRM.dbo.PartyRelationship
    INNER JOIN CRM.dbo.PartyRelationshipKind ON PartyRelationship.PartyRelationshipKindID = PartyRelationshipKind.PartyRelationshipKindID
    AND PartyRelationship.PartyRelationshipKindID = PartyRelationshipKind.PartyRelationshipKindID
    INNER JOIN CRM.dbo.PartyRole ON PartyRelationship.ConsumerPartyRoleID = PartyRole.PartyRoleID
    INNER JOIN CRM.dbo.Party ON PartyRole.PartyID = Party.PartyID
    INNER JOIN CRM.dbo.Person ON Person.PersonID = Party.PartyID
    INNER JOIN CRM.dbo.PersonNameDetails ON PersonNameDetails.PersonID = Person.PersonID
    INNER JOIN CRM.dbo.NameDetails ON PersonNameDetails.NameDetailsID = NameDetails.NameDetailsID
    INNER JOIN CRM.dbo.ClientRole ON ClientRole.ClientRoleID = PartyRole.PartyRoleID
    INNER JOIN CRM.dbo.PersonalClientRole PCRD ON PCRD.PersonalClientRoleID = ClientRole.ClientRoleID
    RIGHT OUTER JOIN CRM.dbo.LinkedAccountAssociation ON LinkedAccountAssociation.LinkedRoleID = PartyRole.PartyRoleID
    LEFT OUTER JOIN CRM.dbo.account ON LinkedAccountAssociation.LinkAccountID = account.ID
    INNER JOIN CRM.dbo.accountTypes ON account.TypeID = accountTypes.TypeID
    INNER JOIN CRM.dbo.portfolio ON portfolio.LinkAccountID = account.ID
    LEFT OUTER JOIN ApplicationManagement.dbo.UserAccount ON UserAccount.LinkedPersonID = PartyRole.PartyRoleID
    LEFT OUTER JOIN ApplicationManagement.dbo.SecureLogin ON SecureLogin.SecureLoginId = UserAccount.UserAccountID
    LEFT OUTER JOIN CRM.dbo.WealthManagerRole ON WealthManagerRole.WealthManagerRoleID = PartyRole.PartyRoleID
    WHERE PartyRelationship.EndDate = '31-Dec-9999'
    AND PartyRelationship.ConsumerPartyRoleID = PartyRole.PartyRoleID
    AND PartyRelationship.PartyRelationshipKindID = 2;

Resources