SOQL Query refactoring

Question

I have a Parent_Object__c with two string fields Number1__c and Number2__c. The Number2__c value for one record might be the same as Number1__c value for another record. My end goal is to populate two look-ups of Parent_Object__c on a Child_Object__c. The records of Child_Object__cis being created from external integration with Parent_Number1__c field.

Conditions to populate to those two look-ups are:

1. Parent_Number1__c = Number1__c field of Parent_Object__c
2. Once you get the above lookup, get the Number2__c field from that parent record and find another parent record that has the same Number1__c value.

On Child_Object__c trigger:

 set<string> parentNum1Values = new set<string>();
    for(Child_Object__c ch:Trigger.New)
    {
        parentNum1Values.add(ch.Parent_Number1__c);
    }
 set<string> parentNum2Values = new set<string>();

 map<string, string> Num1toNum2Map = new map<string, string>();
 map<string, Id> Num1LookUpMap = new map<string, Id>();
 map<string, Id> Num2LookupMap = new map<string, Id>();
    for(Parent_Object__c po:[select Id, Number1__c, Number2__c from Parent_Object__c 
                             where Number1__c IN :parentNum1Values])
    {
       if(po.Number1__c != po.Number2__c){
            parentNum2Values.add(po.Number2__c);
            Num1toNum2Map.put(po.Number1__c, po.Number2__c); //used when assigning lookups
       }

       Num1LookUpMap.put(po.Number1__c, po.Id);
    }
    for(Parent_Object__c po:[select Id from Parent_Object__c 
                             where Number1__c IN :parentNum2Values])
    {
       Num2LookupMap.put(po.Number1__c, po.Id);
    }

Can I refactor those two soql queries into one query?

Answer 1

You're stuck with two queries here. You can't use joins on any field type outside of relationships, and they don't work on hierarchical relationships either (same object as child and parent).