+ 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.