SOQL Search function of Contact Name With Account Parent to Contact Child

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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