[SalesForce] How to do a SOQL left join

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

Best Answer

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

Related Topic