I'm using Salesforce and trying to write a SOQL statement. My tables look like:
Person: [id, name]
Related: [id, personid1, personid2]
In sql, to find all the people someone is related to, I might write something like:
select person2.name from
person person1, related, person person2
where person1.id = 'xyz'
and person1.id = related.personid1
and related.person2 = person2.id
How can I achieve the same result set using a SOQL statement?
For the purposes of this query I'm going to assume your custom objects and fields use the regular Salesforce naming conventions.
If you're querying with a record ID:
select personid2__r.Name from Related__c where personid1__c = 'xxxyyyzzz123123'
Or if you're querying with a name:
select personid2__r.Name from Related__c where personid1__r.Name = 'John Doe'
If you absolutely need to return records of type Person__c, then you could do something like:
select Id, Name from Person__c where Id in (select personid2__c from Related__c where personid1__c = 'xxxyyyzzz123123')
