+ Start a Discussion
snippets@sfsnippets@sf 

How to achive this

List<Accounts> accList = [ Select Id, Name, (Select Id, Name From Contacts) From Account];

 

This will retrun all the accounts and like each account's Contacts

 

Now what i is needed is i want to restrict the accounts who has no contacts to be retrived.

 

Note the Query should be on Account.

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
GlynAGlynA

To expand on Eric's answer, the query will be:

 

List<Account> list_Accounts =
[   SELECT  Id, Name
    FROM    Account
    WHERE   Id NOT IN (SELECT AccountId FROM Contact)
];

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator

All Answers

ericmonteericmonte

You want to do a SOQL Left anti Join.

 

SELECT Name FROM Position_c WHERE Id NOT IN

(SELECT Position__c FROM Job_Application__c)

http://wiki.developerforce.com/page/A_Deeper_look_at_SOQL_and_Relationship_Queries_on_Force.com

snippets@sfsnippets@sf

Thanks 

GlynAGlynA

To expand on Eric's answer, the query will be:

 

List<Account> list_Accounts =
[   SELECT  Id, Name
    FROM    Account
    WHERE   Id NOT IN (SELECT AccountId FROM Contact)
];

If this helps, please mark it as a solution, and give kudos (click on the star) if you think I deserve them. Thanks!

 

-Glyn Anderson
Certified Salesforce Developer | Certified Salesforce Administrator

This was selected as the best answer