Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

up vote 4 down vote accepted

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'
share|improve this answer
add comment

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.

share|improve this answer
add comment

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:

enter image description here

share|improve this answer
    
so no matter my wishes, I will need to add a query within the for loop that I have? –  Saariko Apr 9 at 13:24
1  
I think you can do it just in one query. As a target object i would take Opportunities and Licenses because it have relationsships to other two objects: Select Id, OptiTex_License__r.dongle_id__c From Opportunities_and_Licenses__c Where ofopportunity__c = 'XXXX' –  mast0r Apr 9 at 13:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.