Query to Include Child Junction Records - Didn't Understand Relationship

Question

I have a Junction object name AcctContactAff which creates a many-to-many relationship between Contacts and Accounts and stores (among other things) the title of the person at the account.

AcctContactAff - Contact - Account - Title

Being new to SOQL I am really struggling to get the following to work:

SELECT Id, Name, NPI__c, 
    (SELECT Title__c FROM AcctContactAff__r) 
FROM Contact

When I run this I get the error message: "Didn't understand relationship 'AcctContactAff__r' in FROM part of query call."

I can get the query to work when I do this:

SELECT Contact__r.id, Contact__r.Name, Title__c, Name 
FROM AcctContactAff__c

But I need to run the query from the Parent not the Child object.

Can anyone tell me what is wrong with the first query statement?

Answer 1

You can find the name of the ChildRelationship as follows:

for (ChildRelationship relation : SObjectType.Contact.getChildRelationships())
    if (relation.getChildSObject() == Contact.sObjectType)
        system.debug(relation.getRelationshipName());

Copy this value verbatim and replace AcctContactAff__r in your original query. Often it's pluralized so I would expect the actual name to be AcctContactAffs__r, but you should run the script above in Execute Anonymous to be sure.

Answer 2

You can go to the field's detail page, and you'll see the relationship name. Go to Setup > Customize > (Object) > Fields > (Field Name), or Setup > Create > (Object) > (Field Name), and you'll see the details. In the "Child Relationship Name" field, you'll see the name of the relationship. For custom lookup fields, you have to remember the __r.