[SalesForce] How to SOQL query a Filtered Lookup on same Object

I am trying to a subQuery for records of a specific Opportunity type that lookup via a Filtered Lookup, 'parent' Opportunities of another record type.

This is the latest subquery tried so far:

SELECT Name,Id, (SELECT Primary_Media_Opportunity__c FROM Opportunity.MSA_Lookup__r) 
FROM Opportunity
WHERE (RecordTypeId = '012U0000000a3NM')

the MSA_Lookup__c is the filtered Lookup field for the child Opportunity record type, but when I tried querying, this error was returned:

Didn't understand relationship 'Opportunity.MSA_Lookup__r' in FROM
part of query call. If you are attempting to use a custom
relationship, be sure to append the '__r' after the custom
relationship name.

Is there a syntax for subQuerying child records of the same object type?

Best Answer

You will need to use child relationship name in the subquery .

For the filtered lookup field there will be a child relationship name .

The subquery will use child relationship name with __r.Let's say your child relationship name is MSA_lookups then your inner query will be

Select id,(Select primary_media__c from MSA_lookups__r) from opportunity

Related Solutions

[SalesForce] Select statements in a Multi-join SOQL query

There is no field called lead on the Opportunity. You can see all of the available fields in the Opportunity object documentation.

There is a field called ConvertedOpportunityId on the Lead object which will be set if the Lead has been converted to an Opportunity. You can query from the Lead to the Opportunity field through it. For example:

Select Id, ConvertedOpportunity.Name
From Lead

If you have a custom Lookup field on the Opportunity for a Lead then you would access it by appending the ‘__r’ as the error states. For example: lead__r.ln_score__c.

Salesforce has good explanation of Relationship Queries in their docs that might be helpful.

[SalesForce] How to find the child relationship name

Verify the API Name of your child relationship as follows:

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

Copy the output verbatim and use it where you have Purchases2__r.

Best Answer

Related Solutions

[SalesForce] Select statements in a Multi-join SOQL query

[SalesForce] How to find the child relationship name

Related Topic