SOQL `JOIN` by relationship

Question

I have two queries as follows: Is there a way to JOIN them in one query using the Question__r.Id?

(I know SOQL doesn't have JOINs, but maybe an alternative solution?)

List<Answer__c> l= [SELECT Answer__C,  
                   Question__r.Id, Question__r.Name 
                   FROM Answer__c WHERE Application__c = :appID];

List<Question_Category__c> q = [SELECT Order__c
                      Question__r.Id, Question__r.Name
                      FROM Question_Category__c WHERE Category__c = :catID];

I could make the above 2 queries and add them to a single map as the outcome, but I wanted to see if there were any other elegant solutions.

Adding to the above, is there a way to add the results of a query to a map and specify Question__r.Id as the key?

Answer 1

You can write this as a single query on the parent object, apparently Question__c, with two parent-child subqueries. If would presumably look something like this, although your question doesn't contain all the schema details needed to make sure this is accurate:

SELECT Id, Name, 
       (SELECT Answer__c FROM Answers__r), 
       (SELECT Order__c FROM Question_Categories__r)
FROM Question
WHERE ...

You'd need to verify the child relationship names Answers__r and Question_Categories__r.

You can also include WHERE clauses on the subqueries to filter the child objects returned, if desired. I've omitted them here because it's not completely clear how you're intending to filter this related information if approaching through the parent object.

Note that as written your second query will not compile; you're querying Question_Category__c and assigning to a List<Question__c>.