Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

HI i am newer of sales force

I was fired a quire

select Name,(select Cou.firstname,Cou.lastName from contacts Cou)  from Account

Which display name of account and it's contact details associate with account... It also display empty entry in contacts so i want not null condition in my query....

So i was try following

select Name,(select Cou.firstname,Cou.lastName from contacts Cou) coust from Account where coust != null

and

select Name,(select Cou.firstname,Cou.lastName from contacts Cou)  from Account where (select Cou.firstname,Cou.lastName from contacts Cou) != null

but none of them is work....

Please give me some guidance

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Could you do something like this

select Name,(select Cou.firstname,Cou.lastName from contacts Cou)  from Account
where id in (select accountid from contact)

In other words, limit your initial query to only those accounts which have at least one contact

share|improve this answer
    
Thanks that was helpful to me...:) –  Chintan Jul 28 at 9:28
    
Glad to help. And thanks @eyescream for the ninja edit –  Doug B Jul 28 at 9:42
    
It was just a minor typo, no point in posting my own answer for that when the core is right :) –  eyescream Jul 28 at 10:41

I don't believe that this is possible in SOQL - you don't have the capability to limit the results based on the contents of the subquery. What you'll need to do is query back all of the account records and then process the results, ignoring those that have zero contacts:

List<Account> accs = [SELECT Name,(select Cou.firstname,Cou.lastName from contacts Cou) 
                      from Account];
for (Account acc : accs) 
{
    if (acc.Contacts.size()>0)
    {
       // carry out your processing here - e.g. store in another list
    }
}
share|improve this answer
    
You can make a "subquery" in WHERE clause as long as it hits different object than the one used in FROM. For example this will not compile and has to be rewritten: SELECT Id, Name FROM Account WHERE Id IN (SELECT ParentId FROM Account). But Doug's answer is perfectly fine. –  eyescream Jul 28 at 8:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.