how do I write a where clause in SOQL that contains a field from the child relationship?

Question

I have the following query:

SELECT Id, Name, (select name, Phone, Email FROM Contacts) FROM Account

the table Account has a child relationship with Contacts.

this is the result of the query:

I want to add a where clause to this query, that will filter the contacts and show me only the record of a contact who's name is "Elon Musk".

This is the child record result:

Answer 1

Your nested query works the same way as basic queries. So you just have to add a WHERE clause inside:

SELECT Id, Name, 
    (SELECT Name, Phone, Email 
        FROM Contacts 
        WHERE 
        Name = 'Elon Musk') 
FROM Account