Select foreign key for sub query when there are more than one candidates

Question

In our Asset object, we have the standard Contact field (who, in our case, is the person who purchased the Asset). We also have a second Lookup(Contact) called Member__c who is the beneficary of the Asset, as people can purchase Assets on behalf of other people. (for context, the Asset can represent a Membership - a parent may purchase for their child. In this case, the parent is referenced in the Contact field and the child in the Member__c field). In short, on the Asset object, we have these two fields:

Name	API Name	Data Type
Contact	Contact	Lookup(Contact)
Member Beneficiary	Member__c	Lookup(Contact)

Now, if I want to get all the Assets that applies to a Member (not whether they purchased it or not), and I use this SSOQL:

[SELECT Id, Name__c, Date_First_Joined_BE__c, CreatedDate,
    (SELECT Name, Expiry_Date__c, Effective_From__c
    FROM Assets
    WHERE Expiry_Date__c >= TODAY
    AND Effective_From__c <= TODAY
    ORDER BY Expiry_Date__c DESC)
FROM Contact WHERE ID IN: memberIds]

It joins on the Asset.Contact field, not the Asset.Member__c field - I get, for each person, a list of the Assets (memberships) they purchased, not the memberships that apply to them.

If I try something like:

[SELECT Id, Name__c, Date_First_Joined_BE__c, CreatedDate,
    (SELECT Name, Expiry_Date__c, Effective_From__c
    FROM Assets
    WHERE Expiry_Date__c >= TODAY
    AND Effective_From__c <= TODAY
    AND Member__c = c.Id
    ORDER BY Expiry_Date__c DESC)
FROM Contact c WHERE ID IN: memberIds]

As you would do in SQL, I just get 'unknown error parsing query' (In Dev Console)

So, How do I select the foreign key to join on when there are multiple candidates?

Answer 1

Each foreign key has its own "relationship name". You'll need to check your setup to see what the custom name looks like, but it'll look something like:

[SELECT Id, Name__c, Date_First_Joined_BE__c, CreatedDate,
    (SELECT Name, Expiry_Date__c, Effective_From__c
    FROM Assets__r
    WHERE Expiry_Date__c >= TODAY
    AND Effective_From__c <= TODAY
    ORDER BY Expiry_Date__c DESC)
FROM Contact WHERE ID IN: memberIds]

You can find the custom relationship name in Setup > Object Manager > Contacts > Fields & Relationships > (select the Member__c field).

It's not possible for one object to have the same relationship name more than once, so you'll always have a unique way to address your subquery.