0

I have 2 fields in my Search function with Account Name and Contact Name. The scenario will be, If the Contact Name has a value 'nothing' it will query in the Account to Contact relationship with where clause in contact. So the output will be, the Account which the contact name is equal to 'nothing'.

SOQL Code:

SELECT Id,Name, (SELECT Id,Name FROM Contacts WHERE FirstName = 'nothing' OR LastName = 'nothing') FROM Account

Expected output should be return only the specific 'nothing' in the Contact Name.

BUT when i execute the Query It returns all Account.

Is there any idea to accomplish this scenario?

0

Your current query is getting all Accounts, and gets all Contacts within those Accounts that have a name of 'nothing'.

What you want is a query that only gets the Accounts that have Contacts with a name of 'nothing'.

You could do something like:

Set<Id> accountIds = new Set<Id>();
for(Contact thisContact : [SELECT AccountId FROM Contact WHERE FirstName = 'nothing' OR LastName = 'nothing']) {
    accountIds.add(thisContact.AccountId);
}

List<Account> myList = [SELECT Id FROM Account WHERE Id IN :accountIds];

Add filters and additional logic to suit.

  • Thanks Nick, I appreciate it I already solved but I don't think so if this is the accurate query. But, thanks anyway – Leorah Sumarong Feb 14 at 1:36
0

If I understood your requirement correctly, you want to extract all the Accounts if any of their Contacts meet your criteria.

You may get those account list in below query

SELECT Id,Name FROM Account WHERE Id IN (SELECT AccountId FROM Contact WHERE FirstName = 'nothing' OR LastName = 'nothing')

In your version what it does is extract all the accounts and for each account extract all the Contacts which meet your criteria.

  • It needs to display also the Contact Name – Leorah Sumarong Feb 14 at 1:33
0

I got the SOQL query, but i don't know if this is accurate. If you can simplify this answer much better.

Anyways, here is my query

SELECT Id,Name,BillingCountry,ShippingCountry,Type,CreatedDate, (SELECT Id,Name FROM Contacts WHERE FirstName = 'nothing' OR LastName = 'nothing') FROM Account  WHERE Id IN (SELECT AccountId FROM Contact WHERE FirstName = 'nothing' OR LastName = 'nothing')  ORDER BY Name ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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