PARENT CHILD SOQL

Question

I have this query which is working but is it possible to only return a list of Accounts where company__c exists.

SELECT ID, name, (select company__c from VotingRelatedCompanies__r where Relationship_Status__c = 'Active' )  from account

VotingRelatedCompanies__r is the child relationship name of a lookup(Account).

Goal: Return a list of Accounts where the relationship exists as Active.

Issue: Query returns all Accounts.

Question: Is it possible to structure the query to accomplish goal?

Ultimately I am trying to loop through the Voting_Relation__c and update a field on the Account which is another lookup to Account but on the Account object. I am thinking reducing the number of Account records to process through I can avoid hitting limits such as CPU and/or Query limits.

Answer 1

Yes, you can use a join to find only records which have child records meeting certain criteria.

SELECT ... FROM MyObject__c WHERE Id IN (
    SELECT Lookup__c FROM Child__c WHERE ...
)

So in your case, if your child object is named VotingRelatedCompany__c, it would look like:

WHERE Id IN (
    SELECT Company__c
    FROM VotingRelatedCompany__c
    WHERE Relationship_Status__c = 'Active'
)