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.

Attribution to: Saariko

Possible Suggestion/Solution #1

Unfortunately (or not?) it is only one level that can be cpecified in the query:

In each specified relationship, only one level of parent-to-child relationship can be specified in a query. For example, if the FROM clause specifies Account, the SELECT clause can only specify the Contact or other objects at that level. It could not specify a child object of Contact.

Relationship Queries: Understanding Relationship Query Limitations.

When you try to execute such query like

Select Id, (Select Id, (Select Status From Tasks) From Cases) From Account

you will get the error:

Attribution to: Sergej Utko

Possible Suggestion/Solution #2

With these association objects it usually works best to query the association object. You can then go up the parent relationships one or more levels (up to 5):

Set<Id> dongleIds = new Set<Id>();
for (Opportunities_and_Licenses__c ol : [
        select OfLicense__r.Dongle_id__c
        from Opportunities_and_Licenses__c
        where OfOpportunity__r.Id = '006M00000086xpw'
        and OfLicense__r.Dongle_id__c != null
        ]) {
    dongleIds.add(ol.OfLicense__r.Dongle_id__c);
}

PS Just saw mast0r's comment - it is the same as that.

Attribution to: Keith C

Possible Suggestion/Solution #3

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'

Attribution to: superfell
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/32305