How to Modify JOINs to Show Data in Fields

[cpatte7372]

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 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

Let me know if sample data would help. However, I think the solution is simply modifying with the correct JOIN to show the missing data in field BizFirstName, and BizLastName.

Thanks

[#2]

@cpatte7372 

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

[#3 · Accepted solution]

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;