[SalesForce] How to select a field from a nested query in junction object

Attached is a small schema of 3 objects in my org

  • Opportunity
  • custom object: license
  • Junction object: opportunity and license

enter image description here

While I have the oppty id, I need to get ALL licenses that are related to it, and for each of them, trying get a certain custom field: dongle_id__c

Following single queries work:
Get ID's of licenses that are related to oppty (in the nested sql):

SELECT Id,
(Select OfLicense__c FROM Opportunities_and_Licenses__r)
FROM Opportunity WHERE Id = '006M00000086xpw'

The first nested query : Select OfLicense__c FROM Opportunities_and_Licenses__r gives me the licenses ID's, and for each, I want to grab the Dongle_id__c value

Tried several variations neither work:

The following SQL – returns me the needed data:

select dongle_id__c from OptiTex_License__c where id in (Select OfLicense__c FROM Opportunity_and_License__c WHERE ofopportunity__c ='006M00000086xpw')

But trying to insert that, as the nested SQL returns me a relationship error on the OptiTex_License__c .

SELECT Id,
(select dongle_id__c from OptiTex_License__c where id in (Select OfLicense__c FROM Opportunity_and_License__c WHERE ofopportunity__c ='006M00000086xpw'))
FROM Opportunity WHERE Id = '006M00000086xpw'
  • Also tried to remove the inner/nested: WHERE clause:

    SELECT Id,
    (select dongle_id__c from OptiTex_License__c where id in (Select OfLicense__c FROM Opportunity_and_License__c))
    FROM Opportunity WHERE Id = '006M00000086xpw'

I tried to remove the "Where id in…." and change the Opportunity_and_License__c to it's relationship name

(select dongle_id__c from Opportunities_and_Licenses__r)

That gives me error the Dongle_id__c is "No Such column" on the junction object – correct – it's on the related one.

Best Answer

Given that a single junction object points to a single license, then you should be able to follow that relationship in the sub query, e.g.

SELECT Id,
(Select OfLicense__r.dongle_id__c FROM Opportunities_and_Licenses__r)
FROM Opportunity WHERE Id = '006M00000086xpw'
Related Topic