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
Andreas YannelosAndreas Yannelos 

SOQL Query getting data from LEAD and ACCOUNT

Hi!

I have the query below in the controller of one of my visualforce pages:
  
SELECT Id, Name, Title, Company, Country, CreatedDate, Status FROM Lead WHERE Status='New' AND OwnerId= :UserInfo.getUserID() ORDER BY CreatedDate DESC

What I would like to do some kind of outer join to check if the Lead.Company field exists as a Account.Name so I can show this on the Visualforce page.

Any ideas on how to do this?
Best Answer chosen by Andreas Yannelos
Abdul KhatriAbdul Khatri
Here is the one way you can do since you can only make outer join based on Id's
  
Set<String> names = new Set<STring>();
for(List<Lead> leadList : [SELECT Id, Name, Title, Company, Country, CreatedDate, Status 
                           FROM Lead 
                           WHERE Status='New' AND OwnerId= :UserInfo.getUserID() ORDER BY CreatedDate DESC])
{
    for(Lead lead : leadList)
    {
        names.add(lead.Company);
    }
}

if(names.isEmpty()) return;

List<Account> accounts = [SELECT Id, Name FROM Account WHERE Name IN :names];
system.debug(accounts);

 

All Answers

Abdul KhatriAbdul Khatri
Here is the one way you can do since you can only make outer join based on Id's
  
Set<String> names = new Set<STring>();
for(List<Lead> leadList : [SELECT Id, Name, Title, Company, Country, CreatedDate, Status 
                           FROM Lead 
                           WHERE Status='New' AND OwnerId= :UserInfo.getUserID() ORDER BY CreatedDate DESC])
{
    for(Lead lead : leadList)
    {
        names.add(lead.Company);
    }
}

if(names.isEmpty()) return;

List<Account> accounts = [SELECT Id, Name FROM Account WHERE Name IN :names];
system.debug(accounts);

 
This was selected as the best answer
Ajay K DubediAjay K Dubedi
Hi Andreas,

Please try the following code: 
//Inside method
List<Lead> leadlist = [SELECT Id, Name, Title, Company, Country, CreatedDate, Status FROM Lead WHERE Status='New' AND OwnerId= :UserInfo.getUserID() ORDER BY CreatedDate DESC];
List<Account> acclist = [SELECT Name FROM Account LIMIT 10000];
List<Account> AccountList_SameName = new List<Account>(); 
List<Lead> LeadList_SameName = new List<Lead>(); 
List<Account> AccList_SameName = new List<Account>(); 

for(Account a : acclist){
    for(Lead l : leadlist){
        if(a.Name == l.Company){
            LeadList_SameName.add(l);
            AccList_SameName.add(a);
        }
    }
}

System.debug('Accounts having similar Name as Lead's CompanyName: '+AccList_SameName);
System.debug('Leads having similar CompanyName as Account's Name: '+AccList_SameName);

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.
Thanks,
Ajay Dubedi
Andreas YannelosAndreas Yannelos
Thanks Abdul! Works great!
Deepali KulshresthaDeepali Kulshrestha
Hi Andreas,
Please follow the given below code with the help of these, you can solve your problem, it may be helpful to you. 
List<Lead> leadList = [SELECT Id, Name, Title, Company, Country, CreatedDate, Status FROM Lead];
Map<String,Lead> leadMap = new Map<String, Lead>();
System.debug(leadList);

for(Lead ld : leadList){
    leadMap.put(ld.Company,ld);
}
    System.debug(leadMap);
List<Account> accList = [SELECT Name,Id FROM Account LIMIT 10000];
System.debug(accList);
for(Account ac: accList){
    if(leadMap.containsKey(ac.Name)){
        System.debug('ye-->'+ac);
        System.debug('ye 2-->'+leadMap.get(ac.Name));
    }
}

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks and Regards,
Deepali Kulshrestha