SOQL help with subquery where clause

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

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

Attribution
Source : Link , Question Author : Deployment Failure , Answer Author : Mohith Shrivastava

Leave a Comment