I got these tables in SF and listed a few fields below :

SF_Opportunity (dimension)

  1. ID (Opportunity ID)
  2. TXC_Converge__c
  3. AB2__ABOrderID__c

SF_OpportunityLineItem (dimension)

  1. ID (Line Item Opportunity ID)
  2. OpportunityID
  3. AB2__ABDropID__c (Ad Book Drop ID)
  4. External_AdServer_ID__c

SF_Opportunity_Line_Item_Detail__c (fact)

  1. ID (Line Item Detail ID)
  2. Opportunity__c (Opportunity ID)
  3. Opportunity_Line_Item_ID__c (Line Item Opportunity ID)
  4. Drop_ID__c (Ad Book Drop ID)
  5. Details_Start_Date__c (Date of Data)

Im trying to do a left outer join between SF_Opportunity_Line_Item_Detail__c and SF_OpportunityLineItem to bring ID, Drop_ID_c (from SF_Opportunity_Line_Item_Detail__c ) and corresponding External_AdServer_ID__c ( from SF_OpportunityLineItem)

I was trying something like below and the same is not working. Any help?

SELECT ID,Drop_ID__c (SELECT External_AdServer_ID__c FROM opportunityLineItem) FROM Opportunity_Line_Item_Detail__c

1 Answer 1


Try this:

SELECT ID, (SELECT External_AdServer_ID__c FROM opportunityLineItems),(SELECT ID,Drop_ID__c FROM Opportunity_Line_Item_Details__r)From Opportunity

you can use this also:

SELECT External_AdServer_ID__c, (SELECT Id,Drop_ID__c FROM Opportunity_Line_Item_Details__r) FROM opportunityLineItem

  • Hey Thanks. Just to know. Do we need to include Opportunity table to achieve this. Sorry if Im sounding naive Nov 30, 2017 at 10:19
  • Thanks. Unfortunately, that throws the below error. Error Code: INVALID_TYPE; Message: (SELECT Id,Drop_ID__c FROM Opportunity_Line_Item_Details__r) ^ ERROR at Row:2:Column:28 Didn't understand relationship 'Opportunity_Line_Item_Details__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. Please reference your WSDL or the describe call for the appropriate names.; <br/> Nov 30, 2017 at 13:38

You must log in to answer this question.

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