SOQL inner join between standard object and custom object

Question

I am trying to query data from two related objects but receiving a "Didn't understand relationship 'Case_Comment__c' in FROM part of query call" error

My query is as follows

SELECT Id,
CaseNumber,
Status, (SELECT Text__c FROM Case_Comment__c) 
FROM Case 
WHERE Id IN (Select Case__c from Case_Comment__c)

Case__c in Case_Comment__c equals Id in Case

Answer 1

If you can, you should work the other way around :

SELECT Text__c, Case__c, Case__r.CaseNumber, Case__r.Status FROM Case_Comment__c

If you really want to work with a parent-to-children relationship, you have to use the correct field :

SELECT
    Id,
    CaseNumber,
    Status,
    (SELECT Text__c FROM Case_Comments__r) 
FROM
    Case 
WHERE
    Id IN (Select Case__c from Case_Comment__c)

Notice that I changed the first Case_Comment__c to Case_Comments__r

To be sure that the field really is Case_Comments__r, you can use the workbench : in Utilities, Rest Explorer, use the URL : /services/data/v46.0/sobjects/Case/describe, look at childRelationShips and find the relationshipName

Hope this helps