Search code examples
joinsalesforceapex-codeone-to-onesoql

SOQL query to join one-to-one between two custom objects (Salesforce Apex)

I have two custom objects in Salesforce: Object1 and Object2 Object2 has a lookup field that references to Object1. More than one record in Object2 could have the same Object1 record referenced. I have to build a SOQL query wich makes a join of Object1 and Object2 where the matching is one-to-one.

ie. With those values in Object1 and Object2 I want that result:

With those values in Object1 and Object2 I want that result

The record in Object1 with Id=2 is not in result because it has two records in Object2 that references it. I would like to know how to achieve this with a SOQL query.

Thanks in advance!

Solution

  • because you only want one row you can sneakily use aggregate functions to get you values from an aggregate query.

    select max(id) object2Id, 
       max(name) object2Val, 
       max(object1__r.name) object1Val 
from object2__c 
group by object1__c 
having count(object1__c) =1

    worked for me (using the name field instead of value in my objects, but that shouldn't matter).

    sample query result