+ Start a Discussion
meet.sivameet.siva 

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

 

Jeff MayJeff May

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

meet.sivameet.siva

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.

Jeff MayJeff May

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;

 

 

 

 

meet.sivameet.siva

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.

 

 

liron169liron169

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.

ForceMantis (Amit Jain)ForceMantis (Amit Jain)
I can suggest a solution if Employee_Id__c is a lookup field. Let me know.
meet.sivameet.siva

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

Jeff MayJeff May

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