How to get more fields from another object with an IN statement

Question

I use the following query to get the data, but i also want to receive more fields from the "soco__Volunteer_Assignment__c" object.

SELECT+FIELDS(ALL),Account.Name,Account.Id+FROM+Contact+WHERE+Id+IN+(SELECT+soco__Contact__c+FROM+soco__Volunteer_Assignment__c+WHERE+soco__Status__c='Active'+LIMIT+2+OFFSET+0

How do I receive more fields from this object?

SELECT+FIELDS(ALL),Account.Name,Account.Id+FROM+Contact+WHERE+Id+IN+(SELECT+soco__Contact__c,soco__End_Date__c+FROM+soco__Volunteer_Assignment__c+WHERE+soco__Status__c='Active'+LIMIT+2+OFFSET+0

placing the fieldname "soco__End_Date__c" in the second select query will receive the folowing error message:

[
    {
        "message": "\nWHERE Id IN (SELECT soco__Contact__c,soco__End_Date__c FROM soco__Volunteer_Assignment__c\n                                   ^\nERROR at Row:1:Column:321\nunexpected token: ,",
        "errorCode": "MALFORMED_QUERY"
    }
]

Answer 1

It's a different type of join to retrieve child objects than it is to filter on them. There used to be really good documentation on join syntax, but it seems to have disappeared.

I believe what you already have is a Left Inner Join, and what you need to add is a Left Outer Join. In this terminology, Left indicates a child table, and Right indicates a parent table. Further, Inner indicates joining via WHERE filtering, whereas Outer indicates pulling in records/fields via SELECT clause.

Anyway, what you need should look like:

SELECT FIELDS(ALL), Parent.Field, (SELECT Field__c FROM Children__r)
FROM Contact WHERE Id IN (SELECT Contact_Lookup__c FROM Child__c)

Note the names in the FROM clause are different in the two joins. When pulling child records into your SELECT clause, you need to use the Child Relationship Name, whereas you use the Qualified API Name when joining via your WHERE clause.