Attached is a small schema of 3 objects in my org
- Opportunity
- custom object: license
- Junction object: opportunity and license
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.