I'm trying to join two standard objects, and a custom object. I need to join OpportunityLineItem, Opportunity, and MyObject. All tables have references to each other.

MyObject has OpportunityLineItemID__c which points back to OpportunityLineItem's Id. The OpportunityLineItem has OpportunityId which maps back to Opportunity's Id.

In standard SQL I'd write this as:

FROM OpportunityLineItem as oli
join Opportunity as o
on oli.OpportunityId = o.id
join myobject as m
on m.id = oli.id
where ...

I tried with the __r notation but I get an error about the relations:

Didn't understand relationship 'myobject__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

My SQOL attempt:

SELECT myobject__r.fieldIwantInThisTable 
FROM OpportunityLineItem where ....

I'm leaving out the Opportunity for MVCE.

  • 1
    Does OpportunityLineItem have a relationship field that points to your MyObject__c object? I answered a similar question here about 2.5 years ago that you might find helpful.
    – Derek F
    Sep 20, 2019 at 14:31
  • OpportunityLineItem does not have a custom field that maps to MyObject__c. The id field in that table maps to OpportunityLineItemID__c in myobject__c though. Perhaps I need to run the from myobject__c rather than OpportunityLineItem and then use OpportunityLineItem__r... for the fields I want? Sep 20, 2019 at 14:38

2 Answers 2


Salesforce, for one reason or another, doesn't allow us to make relationship fields that target OLI (OpportunityLineItem).

We can hold an OLI Id in a text field, but that won't allow you to traverse the relationship in SOQL.

Your options here are to break this into two queries (one to pull your Custom_Object__c records based on the Ids of the OLIs you are working with, one to pull the OLIs and related data from their Opportunities), or maintain a relationship field on MyObject__c to Opportunity so you can use two parent-child subqueries (left outer joins).

The two queries method might look like this:

// You'll want this to be a map so that you can easily grab the appropriate OLI when you're
//   working with MyObject__c records.
// To get Opportunity data in a query on OLI, you use Opportunity.<field api name> for each field
// The relationship between OLI and Opportunity is a standard field, OpportunityId.
// The child relationship name for standard relationship fields is usually just <object name>, e.g. Opportunity 
//   (we drop the "Id" bit from the field name)
Map<Id, OpportunityLineItem> olisMap = new Map<Id, OpportunityLineItem>([SELECT Id, UnitPrice, Opportunity.Amount, Opportunity.AccountId FROM OpportunityLineItem WHERE Id IN :oliIds]);

List<MyObject__c> myObjs = [SELECT Id, OtherField__c FROM MyObject__c WHERE OpportunityLineItem_Id__c IN :olisMap.keySet()];

The one query approach might look like this:

// This approach also uses what Salesforce calls a semi-join
// Basically, a subquery in the WHERE clause that tells us what to query in the main query)
// I assume MyObjects__r is the child relationship name between MyObject__c and Opportunity,
//   you'll likely need to change this.
// Newlines here are just for readability
List<Opportunity> oppsList = [
        Id, Amount, 
        (SELECT Id, UnitPrice FROM OpportunityLineItems__c), 
        (SELECT Id FROM MyObjects__r) 
    FROM Opportunity 
    WHERE Id IN (SELECT OpportunityId FROM OpportunityLineItem WHERE Id IN :oliIds)];
  • Thanks, this makes sense. Sep 20, 2019 at 15:26

Perhaps something like this would work:

SELECT Id, OpportunityId, <Other fields> FROM OpportunityLineItem WHERE OpportunityId IN: (SELECT <Opportunity Id Lookup Field> FROM myobject__c)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .