SOQL help with subquery where clause

Question

I have a two tables, service order and a child table called status history (1 to M relationship). I am trying to find all service order's that have a status history record matching 'Order Complete'.

Here is my query

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c
WHERE id = (SELECT id FROM Status_History__r WHERE Name = 'Order Completed')

I think it is pretty self explanitory, however I recieve the following error in the debug console....

Unknown error parsing query

UPDATE:
New query but still gives an error...
SOQL

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c
WHERE id IN (SELECT id FROM Status_History__r WHERE Name = 'Order Completed')

Error

WHERE id IN (SELECT id FROM Status_History__r WHERE Name = 'Order ^ ERROR at Row:3:Column:29 sObject type 'Status_History__r' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name. Please reference your WSDL or the describe call for the appropriate names.

Answer 1

SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c WHERE id IN (SELECT Service_Order__c FROM Status_History__c WHERE Name = 'Order Completed')

The above should work I believe and worth trying

Answer 2

In response to your update, the child relationship is normally called something like Status_Histories__r.

If you go in the UI to custom objects, look at the object Status History, look at the lookup field that it has to Service Order, then you will see the correct child relationship name there.

---

You should have

WHERE ID IN

rather than

WHERE ID =

Answer 3

Please refer the below example:

[SELECT Id, (SELECT Id from *OpportunityLineItems*) //Child relationship name//
FROM Opportunity
WHERE Id IN
(
SELECT OpportunityId
FROM *OpportunityLineItem* //Object API name//
WHERE totalPrice > 10000
)];