SOQL with conditions on children

Question

I'm unable to finish an easy query, I could use some help on this.

I got a junction object called Creche_Zone_de_Tarification__c that links two objects (Account and Zone_de_tarification__c and that second is the one I want to query)

There is a Master-Detail relationship between Zone_de_tarification__c and Creche_Zone_de_Tarification__c, as you can see here :

I have a list of Creche_Zone_de_Tarification__c and want fill a list of Zone_de_tarification__c : I have to retrieve all Zone_de_tarification__c that are linked to a Creche_Zone_de_Tarification__c from the input list.

The relationship between the two objects is called : Creches_de_Zone_de_Tarification__r (cf screenshot juste before)

To describe this part of the project, I insert a list of Creche_Zone_de_Tarification__c, and whant to retrieve whose Zone_de_tarification__c are affected in order to do some treatment.

This works (no condition) :

But when I try to add my where clause, everything goes down :

The error is : No such column 'Creches_de_Zone_de_Tarification__r' on entity 'Zone_de_Tarification__c'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

As I understand the query, I am on a parent object and want the WHERE clause to be on a field on the children, So it should be : WHERE Relationship__r.field IN :(parentField) ?

Answer 1

If you have a list of child ids you want to filter on, your syntax will look as follows:

List<MyObject__c> records = [
    SELECT Id FROM MyObject__c WHERE Id IN (
        SELECT MyObject__c FROM Child__c WHERE Id IN :childIds
    )
];