0

I am having a query like this

SELECT Id, (select Id, Staff__c from Application_Contacts__r)  
FROM Application__c
WHERE Id IN (SELECT Application__c 
             FROM License__c 
             WHERE Id IN (SELECT License__c 
                          FROM monitoring__c 
                          WHERE id in :objMonitoring))

When I run this I am getting error like this

Nesting of semi join sub-selects is not supported

So I splitted the above query into two queries and its working fine

List<License__c> lstLic=[Select Application__c from License__c where id in(select License__c from monitoring__c where id in :objMonitoring)];
                            Set<ID> osetAppID=new Set<ID>();
                            for (License__c oLic : lstLic) {
                                osetAppID.add(oLic.Application__c);
lstApp=[Select Id,(select Id,Staff__c from Application_Contacts__r)  from Application__c where id in : osetAppID];

Is there a way to achieve it in single query.

monitoring__c is having looking up to License__c

and License__c is having look up to Application__c

3
  • What is the relationship between monitoring__c-License__c and License__c and Application__c? Just lookup relationship (1 to many?) or master-detail?
    – arut
    Aug 16, 2020 at 13:15
  • Look up relation ship only Aug 16, 2020 at 16:37
  • I doubt this can be done in a single query. Apex code you wrote would be the way to go. If your query was based on some static field value (like picklist values), then you could have used some relationship field. But, your query is based on IDs and each subquery is trying to find unique records. Any combination of sub-queries in SOQL would result in an error you have encountered already or would end up in duplicate records, both wouldn't fit in your requirement.
    – arut
    Aug 17, 2020 at 4:50

1 Answer 1

0

It's possible in a single query, but only in specific situations.

The long and short of it is that if you're working with Monitoring__c records from another query or from a trigger context variable, then you have (or can get) access to the License__c ids.

Once you gather those, you'll be able to use them in a (syntactically correct) semi-join.

Another option would be to use trigger(s) to propagate the value of Application__c from your license records to your (child) monitoring records.

1
  • Actually I am doing all these stuffs in trigger only(After update,After insert). I am not able to get your point. Can you please elaborate? Aug 16, 2020 at 16:40

You must log in to answer this question.

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