Combine two SOQL queries

Question

I am a beginner concerning SOQL, I hope you can help me. I would like to combine these two queries:

Query 1:

SELECT Id, Category__c, Segment__c
FROM Account
WHERE (Category__c = 'Prospect' AND Segment__c = 'High') OR (Category__c = 'Referrer' AND Segment__c = 'High')

Query 2:

SELECT Account_vod__c, WEEK_IN_YEAR(Call_Date_vod__c), CALENDAR_YEAR(Call_Date_vod__c)
FROM Call2_vod__c
WHERE Call_Date_vod__c = LAST_N_WEEKS:7
GROUP BY Account_vod__c, Call_Date_vod__c

Where the id of Account should equal the Account_vod__c value from Call2_vod__c.

When I combine these, I get something as:

Combined Query:

SELECT Id, Category__c, Segment__c
FROM Account
WHERE id in (select Account_vod__c from Call2_vod__c where Call_Date_vod__c = LAST_N_WEEKS:7) AND ((Category__c = 'Prospect' AND Segment__c = 'High') OR (Category__c = 'Referrer' AND Segment__c = 'High'))

But now I am missing the Week and Year values:

WEEK_IN_YEAR(Call_Date_vod__c), CALENDAR_YEAR(Call_Date_vod__c)

and this part: GROUP BY Account_vod__c, Call_Date_vod__c

How Can I combine these queries and display the right week and calendar data?

Thanks!

Answer 1

If your Account_vod__c is a lookup to Account object, you can fetch fields from Account by typing Account_vod__r.Name etc (with "r" instead of "c" and using a dot).

So try with something like this?

SELECT Account_vod__c, Account_vod__r.Category__c, Account_vod__r.Segment__c, WEEK_IN_YEAR(Call_Date_vod__c), CALENDAR_YEAR(Call_Date_vod__c)
FROM Call2_vod__c
WHERE Call_Date_vod__c = LAST_N_WEEKS:7
GROUP BY Account_vod__c, Account_vod__r.Category__c, Account_vod__r.Segment__c, WEEK_IN_YEAR(Call_Date_vod__c), CALENDAR_YEAR(Call_Date_vod__c)

A similar one that should work for everybody who doesn't have your objects:

SELECT AccountId, Account.Name, COUNT(Id) countOfContactsCreatedThatWeek, WEEK_IN_YEAR(CreatedDate) week, CALENDAR_YEAR(CreatedDate) year
FROM Contact
GROUP BY AccountId, Account.Name, WEEK_IN_YEAR(CreatedDate), CALENDAR_YEAR(CreatedDate)

Answer 2

If you're ok with using two queries, I would start with that. In the combined version you have, you're technically calling two queries anyways. The inner query in the where clause counts as a second query against your governor limits.

If you're using Apex, try:

Map<Id, Account> aMap = new Map<Id, Account>([SELECT Id, Category__c, Segment__c
FROM Account
WHERE (Category__c = 'Prospect' AND Segment__c = 'High') OR (Category__c = 'Referrer' AND Segment__c = 'High')]);

List<Call2_vod__c> callList = [SELECT Account_vod__c, WEEK_IN_YEAR(Call_Date_vod__c), CALENDAR_YEAR(Call_Date_vod__c)
FROM Call2_vod__c
WHERE Call_Date_vod__c = LAST_N_WEEKS:7 and Id in :aMap.keySet()
GROUP BY Account_vod__c, Call_Date_vod__c];