function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
grandersgranders 

SOQL query with a subquery in the where clause


We have created a custom lookup field to relate accounts to our lead objects. Our partners are listed as accounts and we assign leads to them in this way. I am trying to create a SOQL query that will list all leads that belong to an account(partner) or belong to that account's parent. It seems that the following does not work:
 
SELECT Id, Name, Company, Status, City,  State,  PostalCode,  CreatedDate
FROM Lead  
where  D2_owner__c = '0017000000YPkp8'  or D2_owner__c = 
(Select Account.Parent.ID From Account Where id = '0017000000YPkp8') 
 
Any ideas how to make this work? 
SuperfellSuperfell

How about this

 

SELECT Id, Name, Company, Status, City,  State,  PostalCode,  CreatedDate
FROM Lead  
where  D2_owner__c = '0017000000YPkp8'  or D2_owner__r.ParentID = '0017000000YPkp8'
grandersgranders

Hi Simon,

 

Thanks for the reply. That is close but not quite what I am looking for. That query will return all leads where the account is the lead owner, or one of the accounts child accounts is the lead owner but it will not return the leads where the accounts parent account is the lead owner.

 

I wish I could do the following:

 

or D2_owner__r.Child.ID = '0017000000YPkp8' 

 

but it does not work.

 

I am still searching. 

 

 

 

 
prageethprageeth

Hello granders;

I don't know whether this is the answere that you are looking for.

However I took more than one hour to find the meaning of your answere :) (Actually to guess the relationship between Lead and D2_owner__c and Account.)

I still don't know whether I've understood it correctly. 

 

    public Lead[] getLeads(){
        Lead[] leads = [
            SELECT
                Id, Name, Company, Status, City,  State,  PostalCode,  CreatedDate
            FROM
                Lead
            where
                D2_owner__c = '0017000000YPkp8'
            or
                D2_owner__r.parentId = '0017000000YPkp8'
        ];
        return leads;
    }
 
 
 
 
 


grandersgranders
Hi Prageeth,
 
Thanks for taking the time to look through this. Your code looks similar to the reply from Simon in that it will return all leads where the account is the lead owner, or one of the accounts child accounts is the lead owner but it will not return the leads where the accounts parent account is the lead owner.
 
To simplify this, we are using a special field called d2_owner which is a lookup field from the lead object to the account object. Thus if account B is the child of account A then the following query will return all leads where account B is the lead owner, or one of account B's child accounts is the lead owner but it will not return the leads where account A is the lead owner.
 
SELECT Id, Name, Company, Status, City,  State,  PostalCode,  CreatedDate
FROM Lead  
where  D2_owner__c =  [account B.id]  or D2_owner__r.ParentID = [account B.id]  
This is missing all of the leads where account B's parent account (account A) is the lead owner.
SELECT Id, Name, Company, Status, City,  State,  PostalCode,  CreatedDate
FROM Lead  
where  D2_owner__c =  [account B.id]  or D2_owner__c = [account A.id]  
 This is where I was going with :
SELECT Id, Name, Company, Status, City,  State,  PostalCode,  CreatedDate 
FROM Lead  
where  D2_owner__c =  [account B.id]  or D2_owner__c = 
(Select Account.Parent.ID From Account Where id =  [account B.id]) 
I need some way to get the value of the parent account ID in the where clause:
(Select Account.Parent.ID From Account Where id =  [account B.id])  - Does not work.
Any other ideas?