I'm looking to run a query from a custom object (called RS) related to Case (Master Detail) where I need to get a list of the contacts related to the Account on the Case.
I can run the following query from the case and get the information, but I need to the get the same information running it from the RS child object:
SELECT Name, Id
FROM Contact
WHERE AccountId IN (SELECT AccountId FROM Case where id = '500XXXX')
I think it would look something like this:
select name, (select Name, id from Contact) from account where id IN (select Case.AccountId from RS__c where id = 'a1234XXX')
but get the error "inner select field cannot have more than one level of relationships"
Is it even possible to get this info with one query?
Best Answer
You say that your
RS
object is in a Master-Detail relationship withCase
. Assuming thatRS
is the detail side of the relationship, you simply need to loop over yourRS
records and gather the relatedCase
Ids.With that, you should be able to run your query with one small change.
If this code is part of a trigger on your
RS
object, then this is the only query that you'll need. If you're not doing this as part of a trigger (or otherwise don't have access to the Trigger context variables), then you will need another query to grab the required field(s) from your object.+edit:
So you're outside of a trigger context. In that case, there's only one way I can think of the avoid the second query; use a formula field.
Update to edit:
formula fields can't be used in semijoins. There is still merit in the example below, but to use it in your situation, you'd still likely need to query
RS__c
records to obtain the value of the formula field (and then you'd be using code based on my first example).If you have a formula field that pulls down the
AccountId
from theCase
yourRS
record is related to, you could use that in your semi-join like so...While the most common use of a semijoin is where you'd query the object whose Ids you're trying to obtain (i.e.
Account
in this case), it'll work on any object provided that you have a field that holds the appropriate Id on that object (I don't believe you're allowed any hierarchy traversal, up or down, in a semijoin).