Forum Discussion
How to Modify JOINs to Show Data in Fields
- 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;
Hi, Carlton.
With respect to this section:
LEFT OUTER JOIN ApplicationManagement.dbo.UserAccount
ON UserAccount.LinkedPersonID = PartyRole.PartyRoleID
The LEFT OUTER JOIN approach is conceptually fine.
If you're getting no data, it will relate to the ON statement (i.e. the second line above).
It's not practical/possible for us to drill down any further since we'd require a lot of schema definitions and data to reproduce what you're doing.
If you can temporarily include the [partyrole].[partyroleid] column in your SELECT statement, then check separately that its value does indeed exist in [useraccount].[linkedpersonid]. Though from what little we have to work with, I'd expect it doesn't.
Another possibility is you're not comparing the correct columns on the second line above. Again, we can only guess given it's not possible to reproduce without the underlying tables and data.
Cheers,
Lain