SOQL query for related child information

Question

I'm trying to build a SOQL query to show the Name, Services and Contacts for accounts that have a relationship to a particular custom object, but I feel I'm missing something here:

SELECT Name, ServiceAccountAssociation__r.Name,
       Account.Contact.Name 
  FROM Account 
 WHERE ((Id IN (Select Account__c.Id from ServiceAccountAssociation__c)) 
   AND (BillingState = 'MA'))

The error I"m getting is:

SELECT Name,ServiceAccountAssociation__r.Name ^ ERROR at Row:1:Column:13 Didn't understand relationship 'ServiceAccountAssociation__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

The relationship is as follows:

Accounts are related to Service__c (custom object) via the ServiceAccountAssociation__c custom object. There is a master-detail relationship called Service__c on the ServiceAccountAssociation object which connects Service to Account.

I've looked at various salesforce documentation and am getting really lost...

Answer 1

Recommended - Roll Up Summary

If you have a Master-Detail relationship from ServiceAccountAssociation__c to Account, you can create a Service_Count__c roll up summary. Once you create the field, Select Roll-Up Type > COUNT. Then you can query Account directly without needing any cross-object SOQL.

SELECT Name FROM Account
WHERE Service_Count__c > 0
AND BillingState = 'MA'

Alternative - Using Apex

SubQuery

If you are using a junction, you likely have a relationship FROM ServiceAccountAssociation__c TO Account.

You want to find the child relationship, which you can do as follows:

DescribeSObjectResult describe = SObjectType.Account;
for (ChildRelationship relationship : describe.getChildRelationships())
{
    if (relationship.getChildSObject() == PermissionSetAssignment.SObjectType)
        system.debug(relationship.getRelationshipName());
}

Assuming it were ServiceAccountAssociations__r, you could do a sub-query:

SELECT Name, (SELECT Service__c, Service__r.Name FROM ServiceAccountAssociations__r)
FROM Account WHERE ...

Aggregates

You may need to query the junction table itself if you want to figure out which Accounts are tied to any Service__c Try to describe the lookup field from ServiceAccountAssociation__c to Account and get its relationship name. Assuming the field is also called Account__c:

DescribeFieldResult describe = ServiceAccountAssociation__c.Account__c.getDescribe();
system.debug(describe.getRelationshipName());

Assuming it is Account__r, you could make queries against the junction table.

List<AggregateResult> junctionAggregates = [
    SELECT COUNT(Id) records, Account__c FROM ServiceAccountAssociation__c
    WHERE Account__r.BillingState = 'MA'
    GROUP BY Account__c
    HAVING COUNT(Id) > 0
];

Take a look at how the HAVING clause works.

Tying It Together

You can use the two above queries together to list all Service names associated to an Account:

Set<Id> accountIds = new Set<Id>();
for (AggregateResult aggregate : /*aggregateQuery*/)
    accountIds.add(aggregate.get('Account__c'));
List<Account> accounts = [SELECT Name, /*subquery*/ FROM Account WHERE Id IN :accountIds];