I'm having trouble with a nested SOQL query that hits Asset, Contact, and AccountContactRelation. There are two account record types related to contacts and I want to see both, but only one account record type is related to the Asset. Below is my attempt:

   Asset.Name, Asset.Cohort__c, Asset.Account.Name, Contact.Title, Contact.Name,
      From AccountContactRelation
      AccountContactRelation.Account.RecordTypeId = '123456789abc')     FROM Asset

What I'm aiming for: enter image description here

I should add that I am working in Toad Data Point, but have not had any luck with either the developer console editor or the Workbench.

I am relatively new with SOQL and would appreciate any and all help!


You can see all the ChildRelationship values available with the below script. Spoiler alert, there is no such relationship where AccountContactRelation is the child object.

for (ChildRelationship relation : SObjectType.Asset.getChildRelationships())

However, if you do the same for Account, you will see the relationship.

for (ChildRelationship relation : SObjectType.Account.getChildRelationships())
    if (relation.getChildSObject() == AccountContactRelation.sObjectType)
        system.debug(relation.getRelationshipName()); // AccountContactRelations
    if (relation.getChildSObject() == Asset.sObjectType)
        system.debug(relation.getRelationshipName()); // Assets

The relationship name must be used verbatim any time you do a Left Outer Join. Since the Account object is parent to both objects you are looking at, perhaps you could use such a join on both child objects.

List<Account> records = [
        (SELECT Contact.Name, Contact.Title FROM AccountContactRelations),
        (SELECT Name FROM Assets)
    FROM Account
    WHERE Id IN (SELECT AccountId FROM Asset)

Or you could use Contact as the common parent.

List<Contact> records = [
        Name, Title,
        (SELECT Account.Name FROM AccountContactRelations),
        (SELECT Name FROM Assets)
    FROM Contact
    WHERE Id IN (SELECT ContactId FROM Asset)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.