function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
iegieg 

How to structure query to include fields from Account and Lead tables

I can't seem to find any examples where I want to include fields in my query from the Lead table and the related Account table where the ConvertedAccountId matches.  I have tried writing queries but nothing is working.  Isn't this possible?  I can do two queries but I want to only do one and include some fields found in each table.
Best Answer chosen by ieg
Jack VolkovJack Volkov
The lead object has the field, ConvertedAccountId, so to get the Account's lastname field (assuming your org has person accounts enabled), it will be "ConvertedAccount.LastName" like so:
select convertedaccount.lastname from lead where id = '[leadid]'
If you do not have person accounts enabled, you may have meant to get contacts last name which would look like this:
select convertedcontact.lastname from lead where id = '[leadid]'

All Answers

Omveer kundu 8Omveer kundu 8
Lead=[SELECT ConvertedAccountId,Convert__c,Country,CreatedDate,Email,FirstName,Id FROM Lead]
Aabhi BattaAabhi Batta
HI ieg,
[select name,Room__r.name from Booking__c where id='a0I28000002aQaK']

Room__r.name is the field of the lookUp object in Booking__c.... hope you find ur answer
sumit singh 37sumit singh 37
set<Id> convertedAccountset = new set<Id>();
for (Lead ld : Trigger.new) {
                if(ld.isConverted)
                {                
                     convertedAccountset .add(ld.ConvertedAccountId);
                }

            }
if(!convertedAccountset.isEmpty )
{
list<accout> act=[select id , add account field  from account where id IN:  convertedAccountset  ];
}
 
Jack VolkovJack Volkov
This article explains it pretty well - Understanding Polymorphic Keys and Relationships (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_and_polymorph_keys.htm)

Example of querying for the account names from leads that were converted into accounts:
select name, isconverted, convertedaccount.name, convertedaccount.website, convertedaccount.owner.department 
from lead 
where convertedaccountid != null

The gist is the "Id" in the "ConvertedAccountId" lead field is replaced with ".[account field name]".  It can even go a level deeper to get the data for current converted account owner with "ConvertedAccount.OwnerId" becoming "ConvertedAccount.Owner.[owner field name]".
 
iegieg
None of these are what I am looking for.  I just want to create a simple query that includes the Account.LastName and the Lead.LastName where a Lead.ID = '<lead_id_entered_here>'

Isn't this possible?
Jack VolkovJack Volkov
The lead object has the field, ConvertedAccountId, so to get the Account's lastname field (assuming your org has person accounts enabled), it will be "ConvertedAccount.LastName" like so:
select convertedaccount.lastname from lead where id = '[leadid]'
If you do not have person accounts enabled, you may have meant to get contacts last name which would look like this:
select convertedcontact.lastname from lead where id = '[leadid]'
This was selected as the best answer
iegieg
This worked!! Thank you so much.
Is there anyway to go from Account to Lead?  The example above goes from Lead to Account.