I am trying to create a list of all users associated with a certain account and their roles (from the AccountContactRole
table) but i can't figure out how.
My current SOQL is:
Select Id, FirstName, LastName, Phone FROM Contact Where AccountId = '00120000001Q8PB' AND Id IN (Select ContactID from AccountContactRole WHERE AccountId = '00120000001Q8PB')
But the problem is that this only gives me a list of users who are in the subquery (who have roles in the AccountContactRole
table) not all users.
Edit #2: I need a list of all users (from the Contact table) with a left join of the Role field (from the AccountContactRole), not inner join. (as the latter will return results of Contact who only have Roles, the first should return all contact regardless if they have a Role or not)
Best Answer
Since you want to retrieve all the AccountContactRole child records for the contacts, your query should be this:
Then you can access the roles list for each contact in this way:
See documentation on querying child records.