Apex Code Development You need to sign in to do that
Don't have an account?
Star Health 8 Can I do a sub query on a lookup relationship? [Select Id, (SELECT Id, Name, CPQ_Status__c,SBQQ__Account__c FROM Subscriptions__r where CPQ_Status__c='Active') from Account where Id in : account
Hi Guys,
Can i do a subquery on lookup relation.
actually i have to find those account which has active subscriptions.when i am trying to writ the qury like this
The query you provided seems almost correct, but there's a minor issue with the subquery field you're trying to access.
SELECT Id, (SELECT Id, Name, CPQ_Status__c, SBQQ__Account__c FROM Subscriptions__r WHERE CPQ_Status__c = 'Active') FROM Account WHERE Id IN :accountIdsNote that the subquery is enclosed in parentheses and uses the relationship name "Subscriptions__r" to access the related subscriptions object. The WHERE clause in the subquery filters the results to only include active subscriptions.
Related:
https://salesforce.stackexchange.com/questions/205408/soql-inner-query-on-lookup-field
https://developer.salesforce.com/forums/?id=906F000000090ATIAY
If this information helps, please mark the answer as best. Thank you
If you want the account information then just run the query on the custom object.
SELECT Id, Name, CPQ_Status__c, SBQQ__Account__c FROM Subscriptions__r WHERE CPQ_Status__c = 'Active' and SBQQ__Account__c IN :account
* if SBQQ_Account__c is your relationship field else you should use your relationship field instead.
This way you can get all the related account information.
The sub query is only useful when you want the result of the subquery be based on the result of the parent query.
In your case it will return all Accounts from the list of account Ids and the sub query will only have active Subscriptions and not account that have active subscriptions.
But since you need accounts that have active subscriptions you should use the query I mentioned above and don't worry about the nested query.
Hopefully it will help.