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
Jason PaineJason Paine 

Select Accounts based on Lead Owner

Trying to wrap my head around SOQL syntax and Joins.

I have been a SQL Server DBA/Developer for over 10 years and this Apex/SOQL stuff is confusion :-)

 

I need to Select all Account records where the associated Lead's Owner is like 'Queue')

 

First I tried this

SELECT Id FROM Account WHERE Id IN (SELECT Related_Owner__r.Id From Lead WHERE Owner.Name like '%Queue%')

 

But recieve the error "cannot have more than one level of relationships"

 

So I tried this

SELECT ID,(SELECT Owner.Name FROM Leads__r WHERE Owner.Name like '%Queue%') FROM Account 

 

This Runs but since it is a left join it returns all Accounts and any leads that match

 

I would like to do something like this

SELECT ID,(SELECT Owner.Name FROM Leads__r WHERE Owner.Name like '%Queue%') FROM Account  Where Leads__r.Id != null

 

But that does not work

 

How can I refrence a field form the right part of my join in the where clause

 

Best Answer chosen by Admin (Salesforce Developers) 
Daniel Zeidler (DZ)Daniel Zeidler (DZ)

Try this

 

SELECT Id FROM Account WHERE Id IN (SELECT Related_Owner__c From Lead WHERE Owner.Name like '%Queue%')

All Answers

Kiran KurellaKiran Kurella

If you are only interested in getting the Account Id field values then you can try the following query

 

select ConvertedAccountId from Lead where ConvertedAccountId != null and Owner.Name like '%Queue%'

 

If you need to get additional fields from Account object then you can use the following query:

 

select Id, Name from Account where Id in (
select ConvertedAccountId from Lead where ConvertedAccountId != null and Owner.Name like '%Queue%'
)

 

p.s. Please leave your feedback (Kudos), if the suggested solution is working.
Karthikeyan JayabalKarthikeyan Jayabal

Below is my suggestion and it's because Account object doesnot have any reference to Lead directly.

But, Lead table is related directly to the converted tables like ConvertedAccount, ConvertedContact and ConvertedOpportunity.

 

SELECT Id, ConvertedAccount.Id, ConvertedAccount.Name, ConvertedContact.Id, ConvertedContact.Name FROM Lead WHERE IsConverted=true AND Owner.Name LIKE '%Queue%'

To write SOQL, use tools like  Force.com Explorer, SOQL Explorer of AJAX Tools or Schema Browser of Force.com IDE. These toots will help in easily identifying the table structure.
Jason PaineJason Paine

Sorry left some info out

 

We have a lookup field defined on the Lead Table called Related Owner, the child relationship name is Leads

 

I need to return serveral fields from the Account Table not just Id.

 

Thans for the suggestions on the Force.com explorer it is way better then fumbling around in the developer console

 

Thank you!!!

 

 
Daniel Zeidler (DZ)Daniel Zeidler (DZ)

Try this

 

SELECT Id FROM Account WHERE Id IN (SELECT Related_Owner__c From Lead WHERE Owner.Name like '%Queue%')

This was selected as the best answer
Jason PaineJason Paine

Perfect Thanks!