How to SOQL query a Filtered Lookup on same Object?

Question

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?

Answer 1

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