Parent to Child SOQL WHERE clause

Question

I have a simple parent to child query:

SELECT 
id,
(SELECT checkbox__c FROM ChildRelationshipName__r)
FROM Parent__c
WHERE "How do I say (SELECT id FROM ChildRelationshipName__r) = true"

Would it be possible to create WHERE clause using CHILD records?

Answer 1

Assuming the child object name as ChildObjectName__c and lookup API name on child isLookupName__c, your query with where clause would look as:

SELECT Id,
    (SELECT Checkbox__c FROM ChildRelationshipName__r)
FROM Parent__c WHERE 
Id IN (SELECT LookupName__c FROM ChildObjectName__c WHERE checkbox__c = true)

---

Related documentation for reference: Basic SOQL Relationship Queries. Similar query with Account and Contact object:

SELECT Name, (SELECT Name FROM Contacts) 
    FROM Account WHERE 
    Id IN (SELECT AccountId FROM Contact)

Answer 2

I don't think we can use similar to your question where only child object's field will be in where clause. Yes with parent field it can be used similar to Raul has suggested.

If your requirement is to get data based on child's where condition. Changes your SOQL like below

SELECT checkbox__c,Parent__c.Id FROM ChildObject__c where id='record id';