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
WT007WT007 

Self Join in SoQL

Hello,

 

Im trying to accomplish something like this:

 

select a.id,

a.MY_DUPEID__c,

a.FirstName,

b.Firstname as Firstname_Orig

 

from  Lead a

LEFT JOIN 

lead b 

on a.my_dupeid__c =b.id

 

WHERE

MY_DUPEID__c !=NULL

 

This has to be returned in 1 query.

 

Thanks in advance!

SiriusBlackOpSiriusBlackOp

What type of field is this "MY_DUPEID__c"?

 

If it is a lookup then you should be able to say:

select a.id,
a.MY_DUPEID__c,
a.FirstName,
a.my_dupeid__r.Firstname as Firstname_Orig 
from  Lead a
 
 


WT007WT007
The my_dupeid__ is just a custom field that contains the corresponding id field in the same leads table (that is duplicate of current record).  Im trying to write some SOQL to get all these records back in one recordset.
 
Thanks. 


SiriusBlackOpSiriusBlackOp
Well, there is not JOIN in SOQL.  It works different, so you have to rethink the way you get data. For the record, I wish it had a Join too... but it doesn't. You have to use relationship fields like Lookup or MasterDetailLookup.  The rest of the field types are worthless for "joining".  If it's not a defined relationship via a lookup, then it must be a separate query.  At least that is the way I understand it. 
 


SiriusBlackOpSiriusBlackOp
So, What is the field Type for "my_dupeid__c"?
 
Lookup, text, MasterDetail, Formula,... ?


WT007WT007
Just a text field not set up as lookup, masterdetail or formula.