You need to sign in to do that
Don't have an account?
how to join soql query?
Hi,
I have two soql query.
1.) List<AggregateResult> result = [Select Employee_ID__c, Start_Date__c, Count(Amount__c)tcnt, Sum(Amount__c)tvalue from Opportunity_CRM__c group by Employee_ID__c,Start_Date__c];
2.) List<AggregateResult> result1 = [Select Employee_ID__c, Start_Date__c, Count(Amount__c)tcnt1, Sum(Amount__c)tvalue1 from Opportunity_CRM__c where Stage__c = '7-Closed Lost' group by Employee_ID__c,Start_Date__c];
i need to join these two queries such that final list contains
Employee_ID__c, Start_Date__c, Count(Amount__c)tcnt, Sum(Amount__c)tvalue, Count(Amount__c)tcnt1, Sum(Amount__c)tvalue1.
Please help me with code.
Regards,
S.Sivakumar
Here is an excellent reference on SOQL joining to give you the what, why, and how: http://wiki.developerforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com
Hey thanks.
I know about this already.
But my problem is diiferent. From the same table (Custom object - Opportunity_CRM__c) i have to get the data matching different conditions and upload the results into another table (Custom object - Test__c).
For example:
List<AggregateResult> result = [Select Employee_ID__c, Start_Date__c, Count(Amount__c)tcnt, Sum(Amount__c)tvalue, ( Select Count(Amount__c)tcnt1, Sum(Amount__c)tvalue1 from Opportunity_CRM__c where Stage__c = '7-Closed Lost' group by Employee_ID__c, Start_Date__c) from Opportunity_CRM__c group by Employee_ID__c, Start_Date__c];
Here tcnt gives total count of opportunities and tcnt1 gives total count of closed lost opportunities.
But i am getting error when i do this.
So please help me.
Regards,
S.Sivakumar.
I'm not sure why you need them in a single statement to update another table? You can just set the fields on the Test__c as appropriate:
Test__c t = new Test__c(Field1 = q1.tcnt, Field2 = q2.tcnt1);
insert t;
To do that i need tcnt and tcnt1 values.
I am not able to run that query as the compiler is asking me specify the custom relationship between the objects.
I don't think you can do it in single SOQL query.
You can do in the code.
After getting the 2 lists, go over them and combine then in the code.
No, it is not.
Thats the problem i have right now.
There is no relationship between the two custom objects.
I have to get the data for each employee_ID__c from Opportunity_CRM__c based on different condition and populate the values in Test__c.
Is there any way to do that?
Regards,
S.Sivakumar
See my previous post about 2 queries.
1) execute the first query to get a list of the objects/values you need for that condition
2) execute the second query to get a list of the objects/values you need for that condition
3) create a list of Test__c objects setting fields as appropriate from the first two queries