Using result from select query as variable on sub-query

Question

Sorry for the title, I could not come up with a better one.

Basically, I have the following query

SELECT Id, (SELECT Name FROM Prod__c WHERE Id = Case.Id) FROM Case limit 20

So what I want to achieve is to use the result Id from the query on the sub-query. It is some internal SF Id, and the name I need is in Prod__c. When trying it like this I get the following error:

Bind variables only allowed in Apex code

I'm not sure if what I'm trying to do is even possible. But any help is appreciated.

Answer 1

Salesforce does automatically the filter on the sub-queries provided that you use a relationship field to fetch them (Lookup / Master Detail).

Assume you have an sObject called Prod__c and also a relationship field from Prod__c to Case with Api name Case__c (and which "Child Relationship Name" is Prods (You can check chat on the detail of the relationship field)). If that is the case, you can :

SELECT Id, (SELECT Name from Prods__r) FROM Case LIMIT 20

In this case Prods__r is the Child Relationship Name + __r. Note that standard relationships do not need the __r

Edit after the chat

The sObject Case has a relationship to Prod__c through Case_Product__c. He wants to get all cases that have a specific Prod__c (by Id), so the pseudo code would be

Id prodId = 'whateverId';
List<Case> casesForProduct = [SELECT Subject, Case_Product__r.Name FROM Case WHERE Case_Product__c = :prodId];

// additionally consider ordering and limiting the results