How to Modify Joins to Show Results in Fields

Transact-SQL
1

Hello Community,

The following SQL Code will produce the following output

image

I would very much appreciate help modifying the code to show the data in BizName and BizLastName.
Data definitely exists in those fields, I just need help modifying the code to show the missing data in those fields.

The code is as follows:

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

Please let me know if you need sample data. However, I think it should just be a matter of figuring out the correct JOIN that will show the data.

Thanks

2

My guess is you're not joining to the correct column in PartyRole table:

LEFT OUTER JOIN ApplicationManagement.dbo.UserAccount
ON UserAccount.LinkedPersonID = PartyRole.PartyRoleID /* should be some other column?! */

3

Hi Scott,

Thanks for responding and sorry for the delayed response.

I am rewritten the SQL code from scratch.

Thanks again.