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
Michael3.BrownMichael3.Brown 

Need Help Joining a Custom Object and a Standard Object in an SQL Statement

 

Hello, I have a custom Pipeline Tracker object I am using.. On each record, there is a relationship field that relates each Pipeline record to an Account record. What I want to do is gather fields from both my custom Pipeline Tracker Object and the standard Account table, using the Account Name as the join between the tables. However, I'm having trouble joining the tables.

 

Here's the SQL statement I have:

        for (Pipeline_Tracker__c pt: [SELECT p.Category__c, p.Account__c, a.Region__c 
        FROM Pipeline_Tracker__c p, Account a        
        WHERE  RecordType.Name =: channel AND p.Account__c = a.Name])
        {
           
            opportunities.add(pt);
        }

 

I get an error on the last part of the where statement, saying a.Name is not recognized. However, I feel like the error may be at the beginning where I say for (Pipeline_Tracker__c pt). Do I need to get the account object in there somehow? 

 

I would appreciate any help on the matter.

 

Thanks!

Mike 

Best Answer chosen by Admin (Salesforce Developers) 
Jake GmerekJake Gmerek

Michael, you have a relationship so you can do this if I am reading your request properly.  SOQL actually makes this easier than SQL.  

 

Here try this:

 

for (pipeline_tracker__c pt: [SELECT Category__c, Account__c, Account__r.region__c FROM Pipeline_Tracker__c WHERE RecordType.Name =: channel]){

  opportunities.add(pt);

}

 

when you create a relationship field Account__c a relationship (Account__r by default although this can be changed) is created allowing you to travel up the chain so to speak.  You do have to be careful however as Account__c will return the ID of the account that it is related to and not the name of the account.  If you want the name of the account you have to use Account__r.name.

All Answers

MiddhaMiddha

You need to add a colun and if Account__c is a lookup field you have to match the Account name with the name. Currently you are trying to match Account Id with name.

 

Try this:

 

 for (Pipeline_Tracker__c pt: [SELECT p.Category__c, p.Account__c, a.Region__c 
        FROM Pipeline_Tracker__c p, Account a        
        WHERE  RecordType.Name =: channel AND p.Account__r.Name =: a.Name])
        {
           
            opportunities.add(pt);
        }

 
MiddhaMiddha

One more thing, I though a.Name is some variable. You CANNOT specify any field on right side of the query. Right side should be a variable or some value it cannot be some field.
Michael3.BrownMichael3.Brown

Thanks Gulshan.

 

It seems that you cannot combine two fields, which is what I was trying to do. Instead, I had to split it into two separate SQL statements. Whenever a record is read from my custom object, I have to take the Account name, store it as a variable, and then send it to another SQL statement which can utilize the account as a variable in order to get the related information.

 

public void buildLists()    
{        
channel = ApexPages.currentPage().getParameters().get('channel');        

for (Pipeline_Tracker_3__c pt: [SELECT p.Category__c, p.Account__c, p.Engine_Project__c, p.Engine_Model__c         
FROM Pipeline_Tracker_3__c p         
WHERE RecordType.Name =: channel])                
{            
accountid = pt.Account__c;            
buildAccountList();              
opportunities.add(pt);                
}                      
}        

public void buildAccountList()    
{        
for (Account acct: [SELECT Region__c         
FROM Account        
WHERE Id=:accountid])        
{            
accounts.add(acct);        
}    
}

I can then call from these two lists on my VF page as necessary. 

 

Thanks,

Mike

 
Jake GmerekJake Gmerek

Michael, you have a relationship so you can do this if I am reading your request properly.  SOQL actually makes this easier than SQL.  

 

Here try this:

 

for (pipeline_tracker__c pt: [SELECT Category__c, Account__c, Account__r.region__c FROM Pipeline_Tracker__c WHERE RecordType.Name =: channel]){

  opportunities.add(pt);

}

 

when you create a relationship field Account__c a relationship (Account__r by default although this can be changed) is created allowing you to travel up the chain so to speak.  You do have to be careful however as Account__c will return the ID of the account that it is related to and not the name of the account.  If you want the name of the account you have to use Account__r.name.

This was selected as the best answer
Michael3.BrownMichael3.Brown

Awesome. Thanks, Jake! This is much easier than my back end method I was trying. This will save me a lot of hair.

 

Thanks, again!

 

Mike
Jake GmerekJake Gmerek

No problem