SOQL to get list of users related to a specific parent account

Question

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?

Answer 1

The AccountId on the User object should be the same as the Contact.AccountId. see - https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/sforce_api_objects_user.htm#:~:text=ID%20of%20the%20Account%20associated%20with%20a%20Customer%20Portal%20user. (Note the field is not updateable, it is updated when the ContactId field is updated).

So you can just use

SELECT Id, Contact.Name, Contact.AccountId
FROM User
WHERE AccountId IN 
    (SELECT ParentId FROM Account WHERE RecordType.Name = 'RECORD_NAME' AND Id = '...')

Though I would suggest you might want to filter out any of the users that have a NULL AccountId, and where the Account does NOT have a ParentId