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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.