I'm trying to create a query that will fetch all the users that are associated to the parent account of an specific account. Both accounts are PersonAccount
entities.
So, basically I only have the "child account" value, and I need to query the parent and then fetch all the users that have the ContactId associated to the contactId of the parent.
So far I have tried this:
SELECT Id, Name, ContactId, Contact.AccountId
FROM User
WHERE ContactId != NULL AND ContactId IN
(SELECT Parent.PersonContactId FROM Account WHERE RecordType.Name = 'RECORD_NAME' AND Id = '...')
This query throws The inner select field 'Parent.PersonContactId' cannot have more than one level of relationships
So, I tried this one here:
SELECT Id, Contact.Name, Contact.AccountId
FROM User
WHERE Contact.AccountId IN
(SELECT ParentId FROM Account WHERE RecordType.Name = 'RECORD_NAME' AND Id = '...')
But the error now became The left operand 'Contact.AccountId' cannot have more than one level of relationships
How can I obtain that information with a single query?, Is that even possible?