You need to sign in to do that
Don't have an account?
granders
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?
How about this
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.
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;
}
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.