Stack Exchange
Combine two SOQL queries
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!

By the way, there is an extra Salesforce forum on the Stackexchange: http://salesforce.stackexchange.com/

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)


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];


2nd one will be an AggregateResult with it's own set of minor headaches.
