[SalesForce] Contact and AccountContactRole relationship [SOQL]

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:

List<Contact> contacts = [Select Id, FirstName, LastName, Phone, (Select Id FROM AccountContactRoles) FROM Contact Where AccountId = '00120000001Q8PB'];

Then you can access the roles list for each contact in this way:

List<AccountContactRoles roles = contacts[0].AccountContactRoles;

See documentation on querying child records.

Related Topic