2

We have an object that relates to Account (Services). I'm trying to write a SOQL query that can give me accounts that have at least one active such object. Those objects have a checkbox that is checked when inactive. A single account can have dozens of these objects, inactive or not but I'm only interested in getting a list of Accounts that have at least one active.

I'm thinking something like 

[SELECT id, account__r.id, account__r.name from service__c where cb_IsInactive = false]

But this returns me all active services by account. Can I add a filter into this query that only gives me one line per account? Some thing like COUNT() but of course not the same?

Edit: After hitting what I think are governor limits to my query I noticed the new (or semi-new) COUNT_DISTINCT aggregate query. This returned me the total distinct number of accounts, and the rest was done by query all active services as csv, importing to Excel and using the filter functions there to eliminate duplicates.

EDIT2: This here query worked for me, for anyone that has the same or similar problem

select COUNT_DISTINCT(Account__r.Id) from Service__c where chk_Inactive__c =false
Improve this question
1
  • 1
    select id, name from account where id in (select AccountId__c from service__c where cb_IsInative=false) would tht work ?
    – techtrekker
    Apr 17, 2013 at 11:18

2 Answers 2

Reset to default
1

This didn't work but I'd like to add what did:

select COUNT_DISTINCT(Account__r.Id) from Service__c where chk_Inactive__c =false

This returned a count of all the relevant Accounts.

Improve this answer
1
  • 1
    Please update your question with additional information, stackExchange does not function like a forum and the answer functionality is intended for solutions to the question.
    – Samuel De Rycke
    Apr 17, 2013 at 14:00
1

Can you not simply group by Account in your SOQL to query Service__c ? E.g.

SELECT Account__c, COUNT(Id) FROM Service__c 
WHERE cb_IsInactive__c = false
GROUP BY Account__c
HAVING Count(Id) > 0
Improve this answer
4
  • This didn't work, ut I'd like to add what did.
    – akarnid
    Apr 17, 2013 at 13:55
  • Apologies, I missed the __c of the end of cb_IsInactive (fixed now). Other than that, it works. What error message did you get @akarnid?
    – Phil Hawthorn
    Apr 17, 2013 at 14:31
  • I hit some governor limits I think. If i do it directly then without limiting my query then I get "Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch". And it only works correctly when I limit it to 5000 or less. And with over 200.000 services that won't do. Thanks for the help though!
    – akarnid
    Apr 17, 2013 at 15:11
  • ah, of course 'the governor' :) no probs
    – Phil Hawthorn
    Apr 17, 2013 at 17:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .