[SalesForce] SOQL Search function of Contact Name With Account Parent to Contact Child

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?

Best Answer

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.

Related Topic