Forum Discussion
cpatte7372
Apr 05, 2024Copper Contributor
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...
- Apr 05, 2024SELECT
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;
Patooworld
Apr 05, 2024Copper Contributor
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;
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;