8

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.

3 Answers 3

5

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'
4

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.

0
3

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

2
  • so no matter my wishes, I will need to add a query within the for loop that I have?
    – Saariko
    Apr 9, 2014 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' Apr 9, 2014 at 13:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .