You need to sign in to do that
Don't have an account?
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
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
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:
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.
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.
I can then call from these two lists on my VF page as necessary.
Thanks,
Mike
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.
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
No problem