Attached is a small schema of 3 objects in my org
- 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
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.